I am trying to find specific value in a JSON array placed at PostgreSQL DB. I searched for some solutions (visited all the links when u search smth like "find value in json array postgresql") and tried them but I haven't succeeded, here the last one working example for me:

SELECT json_column
FROM table t,
     LATERAL (
         SELECT array_agg(json::text::integer) arr
         FROM json_array_elements(t.json_column -> 'ids') json
         ) subquery;

And this solution just prints out my JSON column. I understand the workflow of json_array_elements and array_agg but LATERAL is magic for me. Maybe it is the cause?

The JSON structure I have:

[
  {
    "ids": [
      12312,
      123123,
      124124,
      124124124,
      12,
      12234
    ]
  }
]

So, I need to understand how to extract values and find the one I need. Thanks for your help in advance!

CodePudding user response：

• Use json_array_elements to unpack the json array
• Join each row with unpacked array elems
• Filter by unpacked elem value

select t.* from my_table t
cross join json_array_elements(json_column->'ids') as j(id)
where j.id::text = '12312';

Update

As there is an outer array, and only one object in the outer array, this modification seems to be sufficent:

select t.* from my_table t
cross join json_array_elements(
    json_array_element(json_column,0)->'ids'
) as j(id)
where j.id::text = '12312';