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
    }
  ]
}

stackoverflow.com/questions/24944347/…

John Watts
– John Watts

2018-04-30 21:47:41 +00:00
Commented Apr 30, 2018 at 21:47 — John Watts
– John Watts, Commented Apr 30, 2018 at 21:47
So do you have your answer?

Erwin Brandstetter
– Erwin Brandstetter

2018-05-04 23:41:28 +00:00
Commented May 4, 2018 at 23:41 — Erwin Brandstetter
– Erwin Brandstetter, Commented May 4, 2018 at 23:41

djkern · Accepted Answer · 2018-04-30 22:33:10Z

0

How about this?

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

answered Apr 30, 2018 at 22:33

djkern

4823 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2018-05-01 03:22:32Z

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:

Collectives™ on Stack Overflow

Select rows with value > x inside nested json array

2 Answers 2

Comments

Comments

Linked

Hot Network Questions