27 Dec, 2023
Based on the ideas in https://shusson.info/post/building-nested-json-objects-with-postgres but for SQLite.
Here's test.sql:
.mode box
CREATE TABLE form (
id integer primary key,
description text
);
CREATE TABLE section (
id integer primary key,
name text,
form_id integer references form(id)
);
CREATE TABLE question (
id integer primary key,
name text,
section_id integer references section(id)
);
INSERT INTO form (description) VALUES (lower(hex(randomblob(16))));
INSERT INTO section (name, form_id) SELECT lower(hex(randomblob(16))), 1 FROM generate_series(1, 10);
INSERT INTO question (name, section_id) SELECT
lower(hex(randomblob(16)))
, floor(s.value/10)
FROM generate_series(10, 100, 2) AS s;
SELECT
form.id AS form_id,
section.id AS section_id,
question.id AS question_id
FROM form
LEFT JOIN section ON section.form_id = form.id
LEFT JOIN question ON question.section_id = form.id
WHERE form.id = 1;
SELECT
id
, (
SELECT
-- json_group_object(name, json_object('id', id, 'questions', questions)) AS sections
json_group_array(json_object('id', id, 'name', name, 'questions', questions)) AS sections
FROM (
SELECT
section.id,
section.name,
(
SELECT
-- json_group_object(name, json_object('id', id))
json_group_array(json_object('id', id, 'name', name))
FROM (
SELECT
question.id,
question.name
FROM question
WHERE question.section_id = section.id
)
) AS questions
FROM section
WHERE section.form_id = form.id
)
) AS json
FROM form;
Run like this:
$rm test.db; cat test.sql | sqlite3 test.db
┌─────────┬────────────┬─────────────┐
│ form_id │ section_id │ question_id │
├─────────┼────────────┼─────────────┤
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 2 │
│ 1 │ 1 │ 3 │
│ 1 │ 1 │ 4 │
│ 1 │ 1 │ 5 │
│ 1 │ 2 │ 1 │
│ 1 │ 2 │ 2 │
│ 1 │ 2 │ 3 │
│ 1 │ 2 │ 4 │
│ 1 │ 2 │ 5 │
│ 1 │ 3 │ 1 │
│ 1 │ 3 │ 2 │
│ 1 │ 3 │ 3 │
│ 1 │ 3 │ 4 │
│ 1 │ 3 │ 5 │
│ 1 │ 4 │ 1 │
│ 1 │ 4 │ 2 │
│ 1 │ 4 │ 3 │
│ 1 │ 4 │ 4 │
│ 1 │ 4 │ 5 │
│ 1 │ 5 │ 1 │
│ 1 │ 5 │ 2 │
│ 1 │ 5 │ 3 │
│ 1 │ 5 │ 4 │
│ 1 │ 5 │ 5 │
│ 1 │ 6 │ 1 │
│ 1 │ 6 │ 2 │
│ 1 │ 6 │ 3 │
│ 1 │ 6 │ 4 │
│ 1 │ 6 │ 5 │
│ 1 │ 7 │ 1 │
│ 1 │ 7 │ 2 │
│ 1 │ 7 │ 3 │
│ 1 │ 7 │ 4 │
│ 1 │ 7 │ 5 │
│ 1 │ 8 │ 1 │
│ 1 │ 8 │ 2 │
│ 1 │ 8 │ 3 │
│ 1 │ 8 │ 4 │
│ 1 │ 8 │ 5 │
│ 1 │ 9 │ 1 │
│ 1 │ 9 │ 2 │
│ 1 │ 9 │ 3 │
│ 1 │ 9 │ 4 │
│ 1 │ 9 │ 5 │
│ 1 │ 10 │ 1 │
│ 1 │ 10 │ 2 │
│ 1 │ 10 │ 3 │
│ 1 │ 10 │ 4 │
│ 1 │ 10 │ 5 │
└─────────┴────────────┴─────────────┘
┌────┬──────────────────────────────────────────────────────────────┐
│ id │ json │
├────┼──────────────────────────────────────────────────────────────┤
│ 1 │ [{"id":1,"name":"aa2db43dd28cb36cf3839c98071fd622","question │
│ │ s":[{"id":1,"name":"881fcced10194953a95e849e957c6bef"},{"id" │
│ │ :2,"name":"bd29f9492552b3b2b6c6188b09e67026"},{"id":3,"name" │
│ │ :"09286bc2c831baffb262c575a81f2c06"},{"id":4,"name":"508f29c │
│ │ 3712fbabd011c48f9b3634a36"},{"id":5,"name":"8723637f3cf82d8d │
│ │ 44f12341f8afe45a"}]},{"id":2,"name":"e34ac88c5ffd3ab63de8c1b │
│ │ 8ba00292f","questions":[{"id":6,"name":"493e130980187c001ae4 │
│ │ 3c857260f6e4"},{"id":7,"name":"7d4e68e6e92636d395f72ac40a478 │
│ │ d2d"},{"id":8,"name":"f22d0fee6c2452a5d3585b321f6c24d8"},{"i │
│ │ d":9,"name":"c2af792f933791c88817be431e2ff602"},{"id":10,"na │
│ │ me":"4605cf18d4557845b90b4be326417ec3"}]},{"id":3,"name":"2f │
│ │ 56cf560a7fa18d9b1d286d01cf3500","questions":[{"id":11,"name" │
│ │ :"9c713fa07cfa6360bfbdf99c81e97601"},{"id":12,"name":"85f03d │
│ │ 329dae93dc32f35f97a37b295c"},{"id":13,"name":"6d54b4df581848 │
│ │ cf0f715c0f9bed7d30"},{"id":14,"name":"9ac68e9a5b8d7cb3998719 │
│ │ 2747badab4"},{"id":15,"name":"45d30a7d4e6cdfab2698b3ce526eda │
│ │ 2b"}]},{"id":4,"name":"88e45eacdf5e07bc018693a228256fae","qu │
│ │ estions":[{"id":16,"name":"772e6085c9208f18c159cd73ad11504f" │
│ │ },{"id":17,"name":"9d9e06c8906329f2805678e6d21dd59f"},{"id": │
│ │ 18,"name":"12daf8cea8109e1abfd64e01c99859fa"},{"id":19,"name │
│ │ ":"eb8a3e9f2b639c60fc40645e69ef7c14"},{"id":20,"name":"972be │
│ │ 784dd46036a372cea08d8a7e49e"}]},{"id":5,"name":"fcea1a0beb4c │
│ │ e26e1574c6b84bc79ff8","questions":[{"id":21,"name":"abeaf40a │
│ │ bd48a36195c3107c893d2c8e"},{"id":22,"name":"df00bde550ba3e79 │
│ │ ec7f2223dc6b59e6"},{"id":23,"name":"a2945cf048e9e6b84130fefa │
│ │ 77813739"},{"id":24,"name":"e121fcdc8a9bdfa0608c12e617bd4df9 │
│ │ "},{"id":25,"name":"dd57e1fe9748da2d53dff6f5b48d81ca"}]},{"i │
│ │ d":6,"name":"bc930d3283ee52dfeb8a3dcd3b1e2290","questions":[ │
│ │ {"id":26,"name":"c888ee1d5a22659514c8d67188ead6d4"},{"id":27 │
│ │ ,"name":"0abbebf64729f9b83f44f3323b9bf021"},{"id":28,"name": │
│ │ "a9fe085a58a3ea26e594cb1eeba46107"},{"id":29,"name":"47defa7 │
│ │ 5a80cc7ac4badbf445b3523fd"},{"id":30,"name":"66fe5f04dd3da96 │
│ │ 551dce024f459f1f7"}]},{"id":7,"name":"f44f99129d0cbb1efe6495 │
│ │ 0b90cf56e5","questions":[{"id":31,"name":"82ef4d44e74cd91041 │
│ │ 94ff167c686d28"},{"id":32,"name":"a24e9e47bb5fd19b581f5ec48d │
│ │ 984aef"},{"id":33,"name":"ada3aab4d9d2139644d9678911428750"} │
│ │ ,{"id":34,"name":"2e4320e9ba22130bbf3da00e61cf35f5"},{"id":3 │
│ │ 5,"name":"f08fbc91e96478044ca5edcd61a2638a"}]},{"id":8,"name │
│ │ ":"36e0ec3a67f4349400e8ec1fb623c90c","questions":[{"id":36," │
│ │ name":"b8522c6a2a294088fdda1ae8e2209c5f"},{"id":37,"name":"3 │
│ │ 7cd5c981a87cfee940b541c1a5dc2b5"},{"id":38,"name":"82fc9f823 │
│ │ e36d45abc2e34acf8594a62"},{"id":39,"name":"42290e56385652ab7 │
│ │ bfdb79361b94644"},{"id":40,"name":"4d5d725c0a3575a08f07f43d8 │
│ │ 4ee250e"}]},{"id":9,"name":"01058cbc0aed92acc3f3200f29451a00 │
│ │ ","questions":[{"id":41,"name":"f921f1dc50250416ce0df4b6cd49 │
│ │ 9486"},{"id":42,"name":"67c7d6b92dcb614dbb146111bf26878d"},{ │
│ │ "id":43,"name":"387e17458ab87df64e09c2dcec8b0710"},{"id":44, │
│ │ "name":"36649771d1a2acbd0ab6dfb2b377eb6c"},{"id":45,"name":" │
│ │ 671e6c0f989dfae7565642cb28ce219c"}]},{"id":10,"name":"112b3c │
│ │ df756cf2186f2d8a91366afdca","questions":[{"id":46,"name":"8c │
│ │ 90977a4bda241f8b5d52b4def75baf"}]}] │
└────┴──────────────────────────────────────────────────────────────┘
Change the use of json_group_array() for json_group_object() to see the differences:
┌────┬──────────────────────────────────────────────────────────────┐
│ id │ json │
├────┼──────────────────────────────────────────────────────────────┤
│ 1 │ {"aa2db43dd28cb36cf3839c98071fd622":{"id":1,"questions":{"88 │
│ │ 1fcced10194953a95e849e957c6bef":{"id":1},"bd29f9492552b3b2b6 │
│ │ c6188b09e67026":{"id":2},"09286bc2c831baffb262c575a81f2c06": │
│ │ {"id":3},"508f29c3712fbabd011c48f9b3634a36":{"id":4},"872363 │
│ │ 7f3cf82d8d44f12341f8afe45a":{"id":5}}},"e34ac88c5ffd3ab63de8 │
│ │ c1b8ba00292f":{"id":2,"questions":{"493e130980187c001ae43c85 │
│ │ 7260f6e4":{"id":6},"7d4e68e6e92636d395f72ac40a478d2d":{"id": │
│ │ 7},"f22d0fee6c2452a5d3585b321f6c24d8":{"id":8},"c2af792f9337 │
│ │ 91c88817be431e2ff602":{"id":9},"4605cf18d4557845b90b4be32641 │
│ │ 7ec3":{"id":10}}},"2f56cf560a7fa18d9b1d286d01cf3500":{"id":3 │
│ │ ,"questions":{"9c713fa07cfa6360bfbdf99c81e97601":{"id":11}," │
│ │ 85f03d329dae93dc32f35f97a37b295c":{"id":12},"6d54b4df581848c │
│ │ f0f715c0f9bed7d30":{"id":13},"9ac68e9a5b8d7cb39987192747bada │
│ │ b4":{"id":14},"45d30a7d4e6cdfab2698b3ce526eda2b":{"id":15}}} │
│ │ ,"88e45eacdf5e07bc018693a228256fae":{"id":4,"questions":{"77 │
│ │ 2e6085c9208f18c159cd73ad11504f":{"id":16},"9d9e06c8906329f28 │
│ │ 05678e6d21dd59f":{"id":17},"12daf8cea8109e1abfd64e01c99859fa │
│ │ ":{"id":18},"eb8a3e9f2b639c60fc40645e69ef7c14":{"id":19},"97 │
│ │ 2be784dd46036a372cea08d8a7e49e":{"id":20}}},"fcea1a0beb4ce26 │
│ │ e1574c6b84bc79ff8":{"id":5,"questions":{"abeaf40abd48a36195c │
│ │ 3107c893d2c8e":{"id":21},"df00bde550ba3e79ec7f2223dc6b59e6": │
│ │ {"id":22},"a2945cf048e9e6b84130fefa77813739":{"id":23},"e121 │
│ │ fcdc8a9bdfa0608c12e617bd4df9":{"id":24},"dd57e1fe9748da2d53d │
│ │ ff6f5b48d81ca":{"id":25}}},"bc930d3283ee52dfeb8a3dcd3b1e2290 │
│ │ ":{"id":6,"questions":{"c888ee1d5a22659514c8d67188ead6d4":{" │
│ │ id":26},"0abbebf64729f9b83f44f3323b9bf021":{"id":27},"a9fe08 │
│ │ 5a58a3ea26e594cb1eeba46107":{"id":28},"47defa75a80cc7ac4badb │
│ │ f445b3523fd":{"id":29},"66fe5f04dd3da96551dce024f459f1f7":{" │
│ │ id":30}}},"f44f99129d0cbb1efe64950b90cf56e5":{"id":7,"questi │
│ │ ons":{"82ef4d44e74cd9104194ff167c686d28":{"id":31},"a24e9e47 │
│ │ bb5fd19b581f5ec48d984aef":{"id":32},"ada3aab4d9d2139644d9678 │
│ │ 911428750":{"id":33},"2e4320e9ba22130bbf3da00e61cf35f5":{"id │
│ │ ":34},"f08fbc91e96478044ca5edcd61a2638a":{"id":35}}},"36e0ec │
│ │ 3a67f4349400e8ec1fb623c90c":{"id":8,"questions":{"b8522c6a2a │
│ │ 294088fdda1ae8e2209c5f":{"id":36},"37cd5c981a87cfee940b541c1 │
│ │ a5dc2b5":{"id":37},"82fc9f823e36d45abc2e34acf8594a62":{"id": │
│ │ 38},"42290e56385652ab7bfdb79361b94644":{"id":39},"4d5d725c0a │
│ │ 3575a08f07f43d84ee250e":{"id":40}}},"01058cbc0aed92acc3f3200 │
│ │ f29451a00":{"id":9,"questions":{"f921f1dc50250416ce0df4b6cd4 │
│ │ 99486":{"id":41},"67c7d6b92dcb614dbb146111bf26878d":{"id":42 │
│ │ },"387e17458ab87df64e09c2dcec8b0710":{"id":43},"36649771d1a2 │
│ │ acbd0ab6dfb2b377eb6c":{"id":44},"671e6c0f989dfae7565642cb28c │
│ │ e219c":{"id":45}}},"112b3cdf756cf2186f2d8a91366afdca":{"id": │
│ │ 10,"questions":{"8c90977a4bda241f8b5d52b4def75baf":{"id":46} │
│ │ }}} │
└────┴──────────────────────────────────────────────────────────────┘
It is a bit easier in PostgreSQL, you can do things like this:
docker run --rm --name postgresql -p 5432:5432 -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=admin -e POSTGRES_DB=demodb -d postgres:latest
docker exec -it postgresql psql -d demodb -U admin
Then:
>>> import psycopg2
>>>
>>> con = psycopg2.connect(database="demodb", user="admin", password="admin", host="localhost", port=5432)
>>> cur = con.cursor()
>>> sql="""
... SELECT to_jsonb(array_agg((SELECT r1 FROM (SELECT
... 1 AS k1,
... 2 AS k2,
... (
... SELECT array_agg((SELECT r2 FROM (SELECT
... 1*s2i AS b,
... 2*s2i AS a
... )r2))
... FROM generate_series(1,4) s2(s2i)
... WHERE s2i <= s1i
... ) AS k3
... )r1)))
... FROM generate_series(1,4) s1(s1i)
... """
>>> cur.execute(sql)
>>> cur.fetchall()[0][0]
[{'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}, {'a': 6, 'b': 3}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}, {'a': 6, 'b': 3}, {'a': 8, 'b': 4}]}]
>>> cur.close()
>>> con.close()
Or with pure Python and the same sql variable:
>>> import pg8000.native
>>>
>>> con = pg8000.native.Connection(database="demodb", user="admin", password="admin", host="localhost", port=5432)
>>> con.run(sql)[0][0]
[{'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}, {'a': 6, 'b': 3}]}, {'k1': 1, 'k2': 2, 'k3': [{'a': 2, 'b': 1}, {'a': 4, 'b': 2}, {'a': 6, 'b': 3}, {'a': 8, 'b': 4}]}]
The important bit is that whilst a row doesn't have any column names, a record does. So by using a record from one select as the input to array_agg you keep the column names all the way through until the to_jsonb() at the end.
Here are some other interesting experiments in PostgreSQL:
CREATE EXTENSION plpgsql;
CREATE TABLE people (
sub text,
firstname text,
lastname text
);
CREATE TYPE ok_type AS (
ok BOOL
);
CREATE DOMAIN ok AS ok_type CHECK ((value).ok is TRUE AND (VALUE).ok IS NOT NULL);
CREATE TYPE profile_type AS (
sub text,
firstname text,
lastname text
);
CREATE DOMAIN profile AS profile_type CHECK((VALUE).sub IS NOT NULL);
CREATE OR REPLACE FUNCTION update_profile(input profile, OUT output ok)
AS $FUNCTION$
BEGIN
UPDATE people p SET
firstname=input.firstname,
lastname=input.lastname
WHERE p.sub = input.sub
RETURNING TRUE INTO STRICT output;
IF NOT FOUND THEN
RAISE EXCEPTION 'UserFriendly: No profile updated'
USING HINT = 'UserFriendly: Are you updating someone else''s profile';
END IF;
END
$FUNCTION$ LANGUAGE plpgsql;
INSERT INTO people (sub, firstname, lastname) VALUES ('8427d5be-691e-4db0-94da-7945e7525442', 'James', 'G');
INSERT INTO people (sub, firstname, lastname) VALUES ('a427d5be-691e-4db0-94da-7945e7525442', 'Paul', 'R');
SELECT ok FROM update_profile(ROW('8427d5be-691e-4db0-94da-7945e7525442', 'Jimmy', 'G')::profile);
SELECT ok FROM update_profile(('8427d5be-691e-4db0-94da-7945e7525442', 'Jimmy', 'G'));
-- Will fail because of the different types (even though the types are the same)
-- CREATE TYPE profile2_type AS (
-- sub text,
-- firstname text,
-- lastname text
-- );
-- CREATE DOMAIN profile2 AS profile2_type CHECK((VALUE).sub IS NOT NULL);
-- SELECT ok FROM update_profile(('8427d5be-691e-4db0-94da-7945e7525442', 'Jimmy', 'G')::profile2);
-- HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT json_agg(people) from people;
select row_to_json(p)
from (
select * from people
) p;
-- Will fail because * isn't a row or record
-- select row_to_json(*)
-- from (
-- select * from people
--);
select row_to_json(people, TRUE) from people;
select pg_typeof(p) from update_profile(('8427d5be-691e-4db0-94da-7945e7525442', 'Jimmy', 'G'))
select to_json(p) from update_profile(('8427d5be-691e-4db0-94da-7945e7525442', 'Jimmy', 'G')) p;
Be the first to comment.
Copyright James Gardner 1996-2020 All Rights Reserved. Admin.