Postgres 15

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 user_phones (
    id serial primary key,
    phones json
);

insert into user_phones (phones) VALUES 
  ('[{"model": "samsung s23 utra","serial_number": "12345"},{"model": "iphone15","serial_number": "12333"}]'),
  ('[{"model_": "samsung s22","serial_Number": "12345","camera": "120Mpix"},{"Model": "iphone14","serial_number": "12333","has_GPS": true}]') 
RETURNING jsonb_pretty(phones::jsonb);

CREATE TABLE




jsonb_pretty


[
    {
        "model": "samsung s23 utra",
        "serial_number": "12345"
    },
    {
        "model": "iphone15",
        "serial_number": "12333"
    }
]

[
    {
        "camera": "120Mpix",
        "model_": "samsung s22",
        "serial_Number": "12345"
    },
    {
        "Model": "iphone14",
        "has_GPS": true,
        "serial_number": "12333"
    }
]



INSERT 0 2


--https://stackoverflow.com/a/77620063/5298879
select id, replace(phones::text,'"serial_number":','"serialNumber":')::json
from user_phones;


id
replace


1
[{"model": "samsung s23 utra","serialNumber": "12345"},{"model": "iphone15","serialNumber": "12333"}]

2
[{"model_": "samsung s22","serial_Number": "12345","camera": "120Mpix"},{"Model": "iphone14","serialNumber": "12333","has_GPS": true}]



SELECT 2


--https://stackoverflow.com/a/77620063/5298879
select id, json_agg(e-'serial_number'||jsonb_build_object('serialNumber', e->'serial_number'))
from user_phones,jsonb_array_elements(phones::jsonb)_(e)
group by 1;


id
json_agg


1
[{"model": "samsung s23 utra", "serialNumber": "12345"}, {"model": "iphone15", "serialNumber": "12333"}]

2
[{"camera": "120Mpix", "model_": "samsung s22", "serialNumber": null, "serial_Number": "12345"}, {"Model": "iphone14", "has_GPS": true, "serialNumber": "12333"}]



SELECT 2


select id, jsonb_pretty(replace(phones::text,'"serial_number":','"serialNumber":')::jsonb)
from user_phones;


id
jsonb_pretty


1
[
    {
        "model": "samsung s23 utra",
        "serialNumber": "12345"
    },
    {
        "model": "iphone15",
        "serialNumber": "12333"
    }
]

2
[
    {
        "camera": "120Mpix",
        "model_": "samsung s22",
        "serial_Number": "12345"
    },
    {
        "Model": "iphone14",
        "has_GPS": true,
        "serialNumber": "12333"
    }
]



SELECT 2


--https://stackoverflow.com/a/77619898/5298879
SELECT id, json_agg(
              json_build_object('model', value->'model', 'serialNumber', value->'serial_number')::json
           ) new_json
FROM user_phones u
CROSS JOIN json_array_elements(u.phones) 
GROUP BY id
--keys lost and nullified


id
new_json


1
[{"model" : "samsung s23 utra", "serialNumber" : "12345"}, {"model" : "iphone15", "serialNumber" : "12333"}]

2
[{"model" : null, "serialNumber" : null}, {"model" : null, "serialNumber" : "12333"}]



SELECT 2

id	replace
1	[{"model": "samsung s23 utra","serialNumber": "12345"},{"model": "iphone15","serialNumber": "12333"}]
2	[{"model_": "samsung s22","serial_Number": "12345","camera": "120Mpix"},{"Model": "iphone14","serialNumber": "12333","has_GPS": true}]

id	json_agg
1	[{"model": "samsung s23 utra", "serialNumber": "12345"}, {"model": "iphone15", "serialNumber": "12333"}]
2	[{"camera": "120Mpix", "model_": "samsung s22", "serialNumber": null, "serial_Number": "12345"}, {"Model": "iphone14", "has_GPS": true, "serialNumber": "12333"}]

id	jsonb_pretty
1	[ { "model": "samsung s23 utra", "serialNumber": "12345" }, { "model": "iphone15", "serialNumber": "12333" } ]
2	[ { "camera": "120Mpix", "model_": "samsung s22", "serial_Number": "12345" }, { "Model": "iphone14", "has_GPS": true, "serialNumber": "12333" } ]

id	new_json
1	[{"model" : "samsung s23 utra", "serialNumber" : "12345"}, {"model" : "iphone15", "serialNumber" : "12333"}]
2	[{"model" : null, "serialNumber" : null}, {"model" : null, "serialNumber" : "12333"}]