Postgres 10

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

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,
    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


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