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 existing_table (
jsonb_column jsonb
);
insert into existing_table values
('{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters": {
"batter": [
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devils Food" }
]
},
"topping": [
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}');
CREATE TABLE
INSERT 0 1
create table mytable (
id varchar(10),
type varchar(10),
name varchar(10),
ppu decimal(5,2)
);
insert into mytable(id, type, name, ppu)
select jsonb_column->>'id' as id,
jsonb_column->>'type' as type,
jsonb_column->>'name' as name,
(jsonb_column->>'ppu')::decimal(5,2) as ppu
from existing_table
CREATE TABLE
INSERT 0 1
create table batters (
id int,
type varchar(20)
);
insert into batters
select (b.value->>'id')::int as id, b.value->>'type' as type
from existing_table
cross join jsonb_array_elements(jsonb_column->'batters'->'batter') as b
CREATE TABLE
INSERT 0 4
select *
from batters
| id | type |
|---|---|
| 1001 | Regular |
| 1002 | Chocolate |
| 1003 | Blueberry |
| 1004 | Devils Food |
SELECT 4