How do I query a JSON array stored in a JSON object?

Question

I have a column with name 'date' of type json.

And need to return all records that contain the "ID" equal "10"

Below is the json structure of the column 'date'

{
  "Name": "ABC",
  "ID": [
    "10",
    "20",
    "30"
  ]
}

How will the query return what I need?

Answer 1

First we create some test data..

CREATE TEMP TABLE foo AS
SELECT
  jsonb_build_object(
    'name', md5(x::text),
    'IDS', ARRAY[
      trunc(random()*10)+1,
      trunc(random()*10)+1,
      trunc(random()*10)+1]
  ) AS bar
FROM generate_series(1,100) AS x;

This generates test data like this.

                               bar                               
-----------------------------------------------------------------
 {"IDS": [10, 3, 1], "name": "c4ca4238a0b923820dcc509a6f75849b"}
 {"IDS": [6, 1, 2], "name": "c81e728d9d4c2f636f067f89cc14862c"}
 {"IDS": [3, 1, 9], "name": "eccbc87e4b5ce2fe28308fd9f2a7baf3"}
 {"IDS": [5, 6, 10], "name": "a87ff679a2f3e71d9181a67b7542122c"}
 {"IDS": [6, 6, 1], "name": "e4da3b7fbbce2345d7772b0674a318d5"}
 {"IDS": [1, 3, 3], "name": "1679091c5a880faf6fb5e6087eb1b2dc"}
 {"IDS": [10, 4, 3], "name": "8f14e45fceea167a5a36dedd4bea2543"}
 {"IDS": [5, 3, 8], "name": "c9f0f895fb98ab9159f51fd0297e236d"}
 {"IDS": [3, 7, 4], "name": "45c48cce2e2d7fbdea1afc51c7c6ad26"}
 {"IDS": [3, 3, 4], "name": "d3d9446802a44259755d38e6d163e820"}

Then to query it you do this

SELECT bar
FROM foo
WHERE bar::jsonb->'IDS' @> '10';