Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table my_pg_table (json_col text);
insert into my_pg_table values ('{ "content": { "objectID": "ABC", "ObjectType": "MyType", "MyArray": [ "Blue", "Black" ], "impressions": [ { "intent": "Large" }, { "intent": "Small" }, { "intent": "Regular" }, { "intent": "Medium" } ] } }')
1 rows affected

select 
json_col::json -> 'content'->>'objectID' as objectID
,json_array_elements(json_col::json -> 'content'->'impressions')->>'intent' as intent
,json_array_elements_text(json_col::json -> 'content'->'MyArray') as MyArrayValue
from my_pg_table 


objectid
intent
myarrayvalue


ABC
Large
Blue

ABC
Small
Black

ABC
Regular
null

ABC
Medium
null


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


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

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