1

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?

2
  • field->'id' @> '"10"' or field->>'id' @> '10' Commented Feb 6, 2017 at 17:10
  • Sorry: field->'ID' @> '"10"' or field->>'ID' @> '10' Commented Feb 6, 2017 at 17:19

1 Answer 1

2

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';
3
  • thanks for the answer, But this did not work for me. The query returns no result. I believe it is because my column is of type 'json' different from its example that is the type 'jsonb'. Is there another solution for my case?
    – maycon
    Commented Feb 6, 2017 at 18:43
  • My query does the casting explicitly WHERE bar::jsonb->'IDS' @> '10'; I'm not sure why your query doesn't work, but I know that is not the reason. Do you have more details you can provide? (You can replace jsonb_build_object with json_build_object and test it for yourself). You shouldn't be casting to jsonb in query either way. It's needlessly slower. Simply ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE jsonb USING (bar::jsonb); Commented Feb 6, 2017 at 18:46
  • I Replaced part @> '10' to @> '["10"]' and working. I do not understand why, but now it's returning. Thanks!
    – maycon
    Commented Feb 6, 2017 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.