By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE YourTable (jsonColumn jsonb)
CREATE TABLE
INSERT INTO YourTable (jsonColumn)
VALUES ('{
"manufacturer":[
{
"manufacturer":"Tesla",
"address":"Deer Creek Road Palo Alto",
"contact":"support@tesla.com"
},
{
"manufacturer":"BMW",
"address":"Petuelring 130, 80809 München",
"contact":"support@bmw.com"
}
]
}')
INSERT 0 1
SELECT j.*
FROM YourTable t
CROSS JOIN jsonb_to_recordset(t.jsonColumn->'manufacturer')
AS j(manufacturer varchar(100), address varchar(100), contact varchar(100));
| manufacturer | address | contact |
|---|---|---|
| Tesla | Deer Creek Road Palo Alto | support@tesla.com |
| BMW | Petuelring 130, 80809 München | support@bmw.com |
SELECT 2