I have a text field named json_col in my postgres (version 10) table. I am trying to expand the two arrays MyArray & impressions into multiple rows using SQL
select
json_col::json -> 'content'->>'objectID' as objectID
,json_array_elements_text(json_col::json -> 'content'->'MyArray') as MyArrayValue
,json_array_elements(json_col::json -> 'content'->'impressions')->>'intent' as intent
from my_pg_table
sample data
{ "content": {
"objectID": "ABC",
"ObjectType": "MyType",
"MyArray": [
"Blue",
"Black"
],
"impressions": [
{
"intent": "Large"
},
{
"intent": "Small"
},
{
"intent": "Regular"
},
{
"intent": "Medium"
}
] } }
In the output, i am getting the outer array (impressions) as expected. Also the first array (MyArray) is expanding to multiple rows, but it does'nt create row for each of the record created by the first array expansion.
i am getting output like this.
objectID intent MyArrayValue
ABC Large Blue
ABC Small Black
ABC Regular [NULL]
ABC Medium [NULL]
But I am looking for an output as below.
objectID intent MyArrayValue
ABC Large Blue
ABC Large Black
ABC Small Blue
ABC Small Black
ABC Regular Blue
ABC Regular Black
ABC Medium Blue
ABC Medium Black
Please let me know if you have any input.
You can try it in this way:
with cte as (
select
json_col::json -> 'content'->>'objectID' as objectID
,json_array_elements(json_col::json -> 'content'->'impressions')->>'intent' as intent
from my_pg_table
),
cte1 as
(
select
json_col::json -> 'content'->>'objectID' as objectID
,json_array_elements_text(json_col::json -> 'content'->'MyArray') as MyArrayValue
from my_pg_table
)
select
t1.objectID,t1.intent,t2.myarrayvalue
from cte t1 inner join cte1 t2 on t1.objectID=t2.objectID
Use the function in lateral joins:
select
json_col::json -> 'content'->>'objectID' as objectID,
impressions->>'intent' as intent,
MyArrayValue
from my_pg_table
cross join json_array_elements(json_col::json -> 'content'->'impressions') as impressions
cross join json_array_elements_text(json_col::json -> 'content'->'MyArray') as MyArrayValue