I am extracting data from a Json in SQL and the data comes in lists, how do I get it from there?

Question

thanks for reading it! I am extracting data from a Json with operators in SQL, this is the code:

select lc.name as company,
       lr.name as retailer,
       ls.name as tienda,
       sm.created_on as date,
       sm.task_id as task_id,
       ss.submission_data::json-> '¿Qué ''bancos'' se encuentran cerca de la tienda? De ser necesario, pregunta al gerente de tienda' AS banco,
       ss.submission_data::json->> 'Indica la distancia aproximada entre la tienda y el ''banco'' más cercano' AS banco_distancia,
       ss.submission_data::json->> '¿En la zona hay disponibilidad de ''transporte público''? Indica los tipos de transporte' AS transporte,
       ss.submission_data::json->> 'Indica el precio estimado de un viaje en ''transporte público''' AS transporte_precio,
       ss.submission_data::json->> '¿En la zona hay servicio de ''aplicaciones de envíos''?' AS app_envio
from submission_submissionmetadata sm
  left join submission_submission ss on sm.submission_id = ss.id
  left join location_store ls on ls.id = sm.store_id
  left join location_retailer lr on lr.id = ls.retailer_id
  left join location_company lc on lc.id = lr.company_id
where sm.brand_id = 293 
order by date desc

In The results I get the columns bank, transport and app_ship come as in a list type, I have tried to use functions to flatten this but I have not been successful. Do you know what I can do?

Answer 1

https://www.postgresql.org/docs/current/functions-json.html

Waiting on your response to comment above, but you can see all the ways to extract json in the above doc. For instance,if you want only the first element of the app_envio array, it would be:

ss.submission_data::json #>>  '{'¿En la zona hay servicio de ''aplicaciones de envíos''?',0}' AS app_envio