Select rows with value > x inside nested json array

Question

I would like to be able to select the rows where "likes" is greater than 50.

 {
  "id":2,
  "name":"TEST",
  "locations":[
    {
      "cityName":"NEW YORK",
      "lat":123, "lon":456,
      "likes":1000,
      "dislikes":5
    }
  ]
}

https://stackoverflow.com/questions/24944347/postgresql-nested-json-querying?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — John Watts, Apr 30 '18 at 21:47

score 0 · Answer 1 · answered Apr 30 '18 at 22:33

0

How about this?

SELECT * FROM mytable WHERE JSON_EXTRACT_PATH_TEXT( jsoncolumn, 'likes' )::INTEGER > 50;

answered Apr 30 '18 at 22:33

djkern

452
3
11

score 0 · Answer 2 · answered May 01 '18 at 03:22

Assuming data type json and that your array always has just the one element like demonstrated:

SELECT *
FROM   tbl
WHERE  (js_column #>> '{locations,0,likes}')::int > 50;

Using the json / jsonb #>> operator that takes a text array as path. Array index starts with 0 in a JSON array (as opposed to Postgres array counting from 1).

If the array can have multiple elements (which is the point of having a array in the first place) and you want any row where at least one element record qualifies, things get more sophisticated:

SELECT *
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM json_array_elements(t.js_column->'locations') elem
   WHERE  (elem->>'likes')::int > 50
   );

Related, with explanation:

Select rows with value > x inside nested json array

2 Answers2