Converting json array value to multiple rows using json_array_elements in postgres

Question

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.

Akhilesh Mishra · Accepted Answer · 2020-08-01 18:27:44Z

1

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

DEMO

answered Aug 1, 2020 at 18:27

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spp Over a year ago

Thank you @Akhilesh. I have an approach as follows as well, somehow i m not able to apply it on top the select statement. I will go ahead with the join approach. select a,b from unnest(array[1,2]) a, lateral unnest(array[1,2,3]) b;

klin · Accepted Answer · 2020-08-01 18:41:28Z

1

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

Db<>fiddle.

answered Aug 1, 2020 at 18:41

klin

123k15 gold badges240 silver badges262 bronze badges

1 Comment

spp Over a year ago

thank you @klin , i have an approach as follows as well, somehow i m not able to apply it on top the select statement. I will go ahead with the join approach. select a,b from unnest(array[1,2]) a, lateral unnest(array[1,2,3]) b;

Collectives™ on Stack Overflow

Converting json array value to multiple rows using json_array_elements in postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related