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?

Why don't you want the array? do you want a specific element of the array? — devintark, Apr 19 '21 at 02:14
this is the json obsject: { "¿Qué 'bancos' se encuentran cerca de la tienda? De ser necesario, pregunta al gerente de tienda":[ "HSBC", "Santander" ] } — Saul Romero, Apr 19 '21 at 15:34

devintark · Answer 1 · 2021-04-19T02:25:45.900

0

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

edited Apr 19 '21 at 02:25

answered Apr 19 '21 at 02:19

devintark

76
5

Thanks @devintark!! and if I want all of them?? – Saul Romero Apr 19 '21 at 03:17
Are you reading the data into some type of memory object? ( like in python or java?) – devintark Apr 19 '21 at 20:29
I'm not sure to understand your question, but I'm running this query in Looker, but the data source is a database. I only use Sql lenguage· – Saul Romero Apr 21 '21 at 00:18
so i guess then whats wrong with the array? you want see all the items that are in the array no? – devintark Apr 21 '21 at 01:37

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

1 Answers1