PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query JSONB column data in Spanner DB

How do we query for a particular fields in an array element inside a json string
The column datatype is JSONB

Sample json

{ "data" : [ 
   { "begin" : "..." },
   { "inter" : "..." },
   { "inter" : "..." },
   { "skip" : ".." },
   { "inter" : "..." },
   { "final": ".."}
}

We need to find the values of "inter" in above json data string

I tried

select colum_name ->> 'data' ->> 1 ->> 'inter'

but the index is not limited to one value , rather range of values as inter key can be anywhere in the list

Tags:

2

Answers


  1. 0

    Try below query that uses jsonb_array_elements(your_column->'data') to unnest the array elements of the JSONB data, allowing us to iterate over each element. Then, the ->> operator is used to extract the text value of the ‘inter’ key from these elements. A WHERE clause is included to filter out elements without the ‘inter’ key and to ensure that the operation is only performed on array data types.

    SELECT jsonb_array_elements(your_column->'data') ->> 'inter' as inter_value
FROM your_table
WHERE jsonb_typeof(your_column->'data') = 'array' AND 
      jsonb_array_elements(your_column->'data') ? 'inter';
    Login or Signup to reply.
  2. 0

    Flatten/unnest the JSON column array using jsonb_array_elements and a lateral join, filter by "inter attribute exists".

    select j ->> 'inter' as inter -- <other expressions here>
from the_table,
lateral jsonb_array_elements(the_json_column::jsonb -> 'data') as j
where j ? 'inter';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search