Tengo una columna json que como datos como este

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

¿Cómo puedo generar un informe que se vería a continuación?

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

Lo intenté como

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

¿Cómo extraer los valores de la clave?

0
gowtham g 23 jun. 2020 a las 23:34

2 respuestas

Yo usaría json[b]_array_elements(). Esto acomoda el caso cuando la matriz contiene más de un objeto (en esta situación, el muelle generaría una fila por objeto incrustado):

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
1
GMB 23 jun. 2020 a las 20:39

Puede usar #> para acceder a todo el objeto desde la matriz y luego acceder a cada tecla:

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

La diferencia entre #> y #>> (lo que usó) es que #> devuelve un valor jsonb nuevamente al que luego se puede acceder más. Mientras que #>> devuelve un valor text.

Alternativamente, puede usar #>> con un paso más en el argumento "ruta":

response #>> '{image_pose_array,0,image_name}' as image_name,
response #>> '{image_pose_array,0,image_pose}' as image_pose
0
a_horse_with_no_name 23 jun. 2020 a las 20:37