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 schema "DOCF000T6";
create table if not exists "DOCF000T6"."assignments" (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
correlation_id text UNIQUE NOT NULL,
referece_id text,
jdata json NOT NULL,
errormassage text
);
insert into "DOCF000T6"."assignments"(correlation_id,jdata)
select 'correlation_id1',
'{
"directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz",
"useragent": "agent-ftps",
"creationdate": "2023-12-12 15:41:03.3",
"data": {
"variables": {
"dms-dokument-name": "Notifiation 22.09.2022.pdf",
"dms-vo-remind": "false",
"dms-messagebox": "E_BN",
"dms-file-number": "0000-15840",
"dms-pio-oe": "1st Duplicate key. Both preserved by JSON, only latest kept in JSONB",
"dms-pio-oe": "2nd Duplicate key. Both preserved by JSON, only latest kept in JSONB",
"first_key": "1st Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...",
"second_key": "2nd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...",
"third_key": "3rd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...",
"fourth_key": "4th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...",
"fifth_key": "5th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,..."
}
}
}' returning *;
CREATE SCHEMA
CREATE TABLE
| id | correlation_id | referece_id | jdata | errormassage |
|---|---|---|---|---|
| 1 | correlation_id1 | null | { "directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz", "useragent": "agent-ftps", "creationdate": "2023-12-12 15:41:03.3", "data": { "variables": { "dms-dokument-name": "Notifiation 22.09.2022.pdf", "dms-vo-remind": "false", "dms-messagebox": "E_BN", "dms-file-number": "0000-15840", "dms-pio-oe": "1st Duplicate key. Both preserved by JSON, only latest kept in JSONB", "dms-pio-oe": "2nd Duplicate key. Both preserved by JSON, only latest kept in JSONB", "first_key": "1st Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "second_key": "2nd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "third_key": "3rd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "fourth_key": "4th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "fifth_key": "5th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,..." } } } |
null |
INSERT 0 1
--pure json without jsonb
update "DOCF000T6"."assignments"
set jdata=(
select replace(json_object_agg(
k1,
case when k1<>'data' then v1
else json_build_object(
'variables',
(select json_object_agg(
k2,
case when k2<>'dms-file-number' then v2
else '"0888-8"'
end
order by n2)
from json_each(v1->'variables')
with ordinality as variables(k2,v2,n2) ))
end
order by n1)::text, '",', E'",\n')::json
from json_each(jdata)with ordinality as top_level_dict(k1,v1,n1)
)
where (jdata#>>'{data,variables,dms-file-number}') = '0000-15840'
returning *;
/*
you should actually add a middle level
to make sure you don't lose additional entries
neighbouring variables under data
- this now assumes there will only ever be variables
as the only key under data
and if there's anything else, this would discard that.
*/
| id | correlation_id | referece_id | jdata | errormassage |
|---|---|---|---|---|
| 1 | correlation_id1 | null | { "directoryRefId" : "00DhDRC53dkoMUsC73znji4UoDUz", "useragent" : "agent-ftps", "creationdate" : "2023-12-12 15:41:03.3", "data" : {"variables" : { "dms-dokument-name" : "Notifiation 22.09.2022.pdf", "dms-vo-remind" : "false", "dms-messagebox" : "E_BN", "dms-file-number" : "0888-8", "dms-pio-oe" : "1st Duplicate key. Both preserved by JSON, only latest kept in JSONB", "dms-pio-oe" : "2nd Duplicate key. Both preserved by JSON, only latest kept in JSONB", "first_key" : "1st Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "second_key" : "2nd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "third_key" : "3rd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "fourth_key" : "4th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "fifth_key" : "5th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,..." }} } |
null |
UPDATE 1
--1. cast to jsonb,
--2. jsonb_set(),
--3. [optionally format with jsonb_pretty()],
--4. cast back to json
update "DOCF000T6"."assignments"
set jdata=jsonb_pretty(
jsonb_set(
jdata::jsonb,
'{data,variables,dms-file-number}',
'"0777-7"' ) )::json
where (jdata#>>'{data,variables,dms-file-number}') = '0888-8'
returning *;
--original order, duplicate keys and custom formatting will is lost
--optional jsonb_pretty() can introduce its own formatting
| id | correlation_id | referece_id | jdata | errormassage |
|---|---|---|---|---|
| 1 | correlation_id1 | null | { "data": { "variables": { "fifth_key": "5th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "first_key": "1st Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "third_key": "3rd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "dms-pio-oe": "2nd Duplicate key. Both preserved by JSON, only latest kept in JSONB", "fourth_key": "4th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "second_key": "2nd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "dms-vo-remind": "false", "dms-messagebox": "E_BN", "dms-file-number": "0777-7", "dms-dokument-name": "Notifiation 22.09.2022.pdf" } }, "useragent": "agent-ftps", "creationdate": "2023-12-12 15:41:03.3", "directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz" } |
null |
UPDATE 1
alter table "DOCF000T6"."assignments" alter column jdata type jsonb;
update "DOCF000T6"."assignments"
set jdata['data']['variables']['dms-file-number']='"0999-9"'--here = assigns
where jdata['data']['variables']['dms-file-number']='"0777-7"'--here = compares
returning id,correlation_id,referece_id,jsonb_pretty(jdata),errormassage;
ALTER TABLE
| id | correlation_id | referece_id | jsonb_pretty | errormassage |
|---|---|---|---|---|
| 1 | correlation_id1 | null | { "data": { "variables": { "fifth_key": "5th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "first_key": "1st Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "third_key": "3rd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "dms-pio-oe": "2nd Duplicate key. Both preserved by JSON, only latest kept in JSONB", "fourth_key": "4th Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "second_key": "2nd Preserved key order in JSON: first,second,third..., reordered in JSONB: fifth,first,...", "dms-vo-remind": "false", "dms-messagebox": "E_BN", "dms-file-number": "0999-9", "dms-dokument-name": "Notifiation 22.09.2022.pdf" } }, "useragent": "agent-ftps", "creationdate": "2023-12-12 15:41:03.3", "directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz" } |
null |
UPDATE 1