extract jsonb value from json containing json array in postgres

Question

I have json column which as data like this-

{"image_pose_array": [{"image_name": "0026568143_WS.jpg", "image_pose": "EXTRA", "is_blurred": false, "is_dark": false}], "policy_number": "\"D000000000\""}

how can i generate a report which would look like below -

image_name        | image_pose    |
-----------------------------------
0026568143_WS.jpg |  EXTRA        |

I tried as

SELECT  response #>>'{image_pose_array,0}' as json
FROM abcd_abcd.table_data
where policy_number ='D017447997' 
order by request_id  asc;

how to extract key's values from it?

Answer 1

You can use #> to access the whole object from the array and then access each key:

SELECT response #> '{image_pose_array,0}' ->> 'image_name' as image_name, 
       response #> '{image_pose_array,0}' ->> 'image_pose' as image_pose
from abcd_abcd.table_data
where policy_number ='D017447997' 
order by request_id  asc;

The difference between #> and #>> (what you used) is, that #> returns a jsonb value again which can then be accessed further. Whereas #>> returns a text value.

Alternatively you can use the #>> with one more step in the "path" argument:

response #>> '{image_pose_array,0,image_name}' as image_name,
response #>> '{image_pose_array,0,image_pose}' as image_pose

Answer 2

I would use json[b]_array_elements(). This accommodates the case when the array contains more than one object (in this situation, the query would generate one row per embedded object):

select 
    t.request_id,
    x.obj ->> 'image_name' as image_name,
    x.obj ->> 'image_pose' as image_pose
from digit_bots.t_fourwheeler_analysis_data t
cross join lateral jsonb_array_elements(t.response -> 'image_pose_array') as x(obj) 
where policy_number ='D017447997' 
order by request_id desc