Skip to content

Instantly share code, notes, and snippets.

@matheusoliveira

matheusoliveira/history.sql

Last active April 26, 2023 14:48
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save matheusoliveira/145f97023f3f701d379f6ec8c11fdb7d to your computer and use it in GitHub Desktop.
Save matheusoliveira/145f97023f3f701d379f6ec8c11fdb7d to your computer and use it in GitHub Desktop.
Download ZIP
PGConf.Brasil 2022 - JSON on PostgreSQL, from zero to hero
Raw
history.sql
/**
* Este arquivo contém o histórico de TODOS comandos que executei durante o treinamento.
* Pode conter alguns erros e falhas, mas fica aqui caso eu tenha esquecido de deixar algum exemplo
* mais claro nos demais arquivos.
*/
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
\e
select '{"a":1, "b":2}'::jsonb;
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1, "b":2, "b": 3}'::json -> 'b';
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1,"b":2,"b": 3}'::json;
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1, "b":2, "b": 3'::json;
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1, "b":2, "b": 3}'::jsonb;
select '{x"a":1, "b":2, "b": 3}'::jsonb;
select '{"a":1, "b":2, "b": 3}'::jsonb;
select '{"a":1, "b":2, "b": 3}'::jsonb::json;
select jsonb_pretty('{"a":1, "b":2, "b": 3}'::jsonb);
select pg_typeof(jsonb_pretty('{"a":1, "b":2, "b": 3}'::jsonb));
select '{"a":1, "b":2}'::jsonb -> 'b';
select '{"a":"foo", "b":"bar"}'::jsonb -> 'b';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b';
select pg_typeof('{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b');
select '{"a":"foo", "b":"bar"}'::jsonb -> 'b';
select '{"a":"foo", "b":"bar"}'::jsonb ->> 'b';
select '{"a":"foo", "b":"bar"}'::jsonb['b'];
select ('{"a":"foo", "b":"bar"}'::jsonb)['b'];
select '{"a":"foo", "b":"bar"}'::jsonb -> 'b';
select '{"a":"foo", "b":"bar"}'::jsonb ->> 'b';
select ('{"a":"foo", "b":"bar"}'::jsonb)['b'] #>> '{}';
select '{"a":"foo", "b":"bar"}'::jsonb ->> 'b';
select (('{"a":"foo", "b":"bar"}'::jsonb)['b'])::text;
select (('{"a":"foo", "b":"ba\"r"}'::jsonb)['b'])::text;
select ('{"a":"foo", "b":"bar"}'::jsonb)['b'] #>> '{}';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b' -> 'c';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb ->> 'b' ->> 'c';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b' ->> 'c';
select (('{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b') ->> 'c');
select '{"a":"foo", "b":{"cd":"bar"}}'::jsonb -> 'b' ->> 'c' || 'd';
select ('{"a":"foo", "b":{"cd":"bar"}}'::jsonb -> 'b' ->> 'c') || 'd';
select '{"a":"foo", "b":{"cd":"bar"}}'::jsonb -> 'b' ->> ('c' || 'd');
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb -> 'b' ->> 'c';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #> '{b,c}';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #>> '{b,c}';
select array['b','c'];
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #>> ('{b,'||'c'||'}');
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #>> ('{b,'||'c"'||'}')::text[];
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #>> array['b', 'c"'];
select '{"a":"foo", "b":{"c\"":"bar"}}'::jsonb #>> array['b', 'c"'];
select '{"a":"foo", "b":{"c''\"":"bar"}}'::jsonb #>> array['b', 'c''"'];
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #>> '{b,c}';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #> '{b,c}';
select '"foo"'::jsonb;
select '"foo"'::jsonb #>> '{}';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #> '{b,c}';
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #> array['b', 'c'];
select '{"a":"foo", "b":{"c":"bar"}}'::jsonb #> '{b,c}'::text[];
select '{"a": [1, 2, 3, 4, 5]}'::json -> 'a';
select '{"a": [1, 2, 3, 4, 5]}'::json -> 'a' -> 0;
select '{"a": 1, "b": 2}'::json @> '{"a": 1}';
select '{"a": 1, "b": 2}'::json @> '{"a": 1}'::json;
select '{"a": 1, "b": 2}'::jsonb @> '{"a": 1}'::jsonb;
select '{"a": 1, "b": 2}'::jsonb = '{"a": 1}'::jsonb;
select '{"a": 1, "b": 2}'::json = '{"a": 1}'::json;
select '{"a": 1, "b": 2}'::json::text = '{"a": 1}'::json::text;
select '{"a": 1, "b": 2}'::json::text = '{"a": 1, "b": 2}'::json::text;
select '{"a": 1, "b": 2}'::json::text = '{"a": 1, "b": 2}'::json::text;
select '{"a": 1, "b": 2}'::jsonb @> '{"a": 1}'::jsonb;
select '{"a": 1, "b": 2, "c": ["foo", "bar", "baz"]}'::jsonb @> '{"a": 1, "c": ["foo"]}'::jsonb;
select '{"a": 1, "b": 2, "c": ["foo", "bar", "baz"]}'::jsonb @> '{"a": 1, "c": ["foo", "zaz"]}'::jsonb;
select '{"a": 1, "b": 2, "c": ["foo", "bar", "baz"]}'::jsonb @> '{"a": 1, "c": ["foo", "baz"]}'::jsonb;
select * from students;
\d students
select * from students;
select * from students s where s.data @> '{"languages": ["pt_BR"]}';
\x
\x
select jsonb_pretty(s.data) from students s where s.data @> '{"languages": ["pt_BR"]}';
select jsonb_pretty(s.data) from students s where s.data @> '{"languages": ["pt_BR"]}';
\e
select jsonb_pretty(s.data) from students s
where s.id = 'bf352c8b-de34-46f2-9ba0-e4cd278eeabd'
and s.data @> '{"languages": ["pt_BR"]}'
;
\e
select jsonb_pretty(s.data) from students s
where
s.data->>'name' = 'Chelidon Commodus'
;
\e
select jsonb_pretty(s.data) from students s
where
s.data->>'name' = 'Chelidon Commodus'
;
select * from students_big limit 10;
select count(*) from students_big;
select * from students_big s where s.data->>'name' = 'Strauss';
explain analyze select * from students_big s where s.data->>'name' = 'Strauss';
create index on students_big ((data->>'name'));
explain analyze select * from students_big s where s.data->>'name' = 'Strauss';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss"}';
explain analyze select * from students_big s where s.data->>'name' = 'Strauss';
analyze select * from students_big s where s.data @> '{"name": "Strauss"}';
select * from students_big s where s.data @> '{"name": "Strauss"}';
explain analyze select * from students_big s where s.data->>'id' = 'f2a7b2ef-68e0-46da-9ccf-7857c1c4b39d';
create index on students_big using gin(data jsonb_path_ops);
explain analyze select * from students_big s where s.data @> '{"name": "Strauss"}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "id": "7edd9eb0-c29c-4e92-93d3-9005bf7415ca"}';
\di+ students_big_*
\dt+ students
\dt+ students_big
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "id": "7edd9eb0-c29c-4e92-93d3-9005bf7415ca"}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"]}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"]}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"], "grades": {"code": 10}}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"], "grades": [{"code": 10}]}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"], "grades": [{"code": 10}]}';
explain analyze select * from students_big s where s.data @> '{"name": "Strauss", "languages": ["pt_BR"], "grades": [{"code": 10}, {"code": 9}]}';
select '{"a": 1}'::jsonb ? 'a';
\dt norm.
select * from norm.students s;
\e
select
s.id, s.name,
s
from norm.students s;
\e
select
s.id, s.name,
to_jsonb(s)
from norm.students s;
select
s.id, s.name,
to_jsonb(s)
from norm.students s\e
select
s.id, s.name,
jsonb_pretty(to_jsonb(s))
from norm.students s
where s.id = '15f7add7-a67d-4c08-8954-759f473b6af5';
\e
select
s.id, s.name,
jsonb_pretty(to_jsonb(s)),
e.*
from
norm.students as s
, jsonb_each(to_jsonb(s)) as e
where s.id = '15f7add7-a67d-4c08-8954-759f473b6af5';
\e
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each(to_jsonb(s)) as e
where s.id = '15f7add7-a67d-4c08-8954-759f473b6af5';
\e
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where s.id = '15f7add7-a67d-4c08-8954-759f473b6af5';
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value)
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
jsonb_object_agg(e.key, e.value)
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
select
s.id, s.name,
jsonb_object_agg(e.key, e.value)
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
;
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '16f7add7-a67d-4c08-8954-759f473b6af5'
;
select
s.id, s.name,
e.*
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where s.id = '15f7add7-a67d-4c08-8954-759f473b6af5';
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value)
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level
jsonb_object_agg(e.key, e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(e.key, e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 5), e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id;
\e
select
t.id, t.name,
t.top_level || jsonb_build_object(
'address', t.addr
)
from (
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id
) t
;
\e
select
t.id, t.name,
jsonb_pretty(t.top_level || jsonb_build_object(
'address', t.addr
))
from (
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id
) t
;
\e
select
t.id, t.name,
jsonb_pretty(t.top_level || jsonb_build_object(
'address', t.addr
))
from (
select
s.id, s.name,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as addr
from
norm.students as s
, jsonb_each_text(to_jsonb(s)) as e
where
s.id = '15f7add7-a67d-4c08-8954-759f473b6af5'
group by
s.id
) t
;
\e
select
s.id,
jsonb_pretty(jsonb_build_object(
'id', s.id,
'name', s.name,
'address', jsonb_build_object(
'city', jsonb_build_object(
'name', s.addr_city,
'state', s.addr_state,
'country', s.addr_country
),
'street', s.addr_street,
'number', s.addr_number,
'geo', jsonb_build_object(
'latitude', s.addr_latitude,
'longitude', s.addr_longitude
)
),
'languages', lang.languages,
'grades', grds.grades
))
from
norm.students s
left join lateral (
select
jsonb_agg(l.language) as languages
from
norm.students_languages l
where
s.id = l.student_id
) as lang on true
cross join lateral (
select
jsonb_agg(
jsonb_build_object(
'code', g.code,
'grade', g.grade
)
) as grades
from
norm.students_grades g
where
s.id = g.student_id
) as grds
;
select * from students;
select * from students s where s.data @? '$.grades[*] ? (@.code == 10 && @.grade > 50)';
\e
select * from students s
where s.data @? '$.grades[*] ? (@.code == 10 && @.grade > 50)';
\e
EXPLAIN
SELECT * FROM students_big
WHERE data @? '$ ? (@.name == "Fiddletown").languages[*] ? (@ like_regex "^pt")';
SELECT * FROM students_big
WHERE data @? '$ ? (@.name == "Fiddletown").languages[*] ? (@ like_regex "^pt")';
explain analyze
SELECT * FROM students_big
WHERE data @? '$ ? (@.name == "Fiddletown").languages[*] ? (@ like_regex "^pt")';
Raw
sample-json-build.sql
-- jsonb_build_object
select
jsonb_build_object(
'id', s.id,
'name', s.name,
'address', jsonb_build_object(
'city', jsonb_build_object(
'name', s.addr_city,
'state', s.addr_state,
'country', s.addr_country
),
'street', s.addr_street,
'number', s.addr_number,
'geo', jsonb_build_object(
'latitude', s.addr_latitude,
'longitude', s.addr_longitude
)
)
),
s.id,
s.name,
to_json(s)
from
norm.students s
;
-- dynamically working with jsonb_each and jsonb_object_agg - group by version
select
t.id,
jsonb_pretty(t.top_level || jsonb_build_object('address', t.address)) as student
from (
select
s.id,
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as address
from
norm.students s
, jsonb_each_text(to_jsonb(s)) as e
group by
s.id
) t
;
-- dynamically working with jsonb_each and jsonb_object_agg - lateral version
select
s.id,
jsonb_pretty(t.top_level || jsonb_build_object('address', t.address)) as student
from
norm.students s
cross join lateral (
select
jsonb_object_agg(e.key, e.value) filter(where e.key not like 'addr_%') as top_level,
jsonb_object_agg(substr(e.key, 6), e.value) filter(where e.key like 'addr_%') as address
from
jsonb_each_text(to_jsonb(s)) as e
) t
;
-- students + languages
select
s.id,
jsonb_pretty(jsonb_build_object(
'id', s.id,
'name', s.name,
'address', jsonb_build_object(
'city', jsonb_build_object(
'name', s.addr_city,
'state', s.addr_state,
'country', s.addr_country
),
'street', s.addr_street,
'number', s.addr_number,
'geo', jsonb_build_object(
'latitude', s.addr_latitude,
'longitude', s.addr_longitude
)
),
'languages', lang.languages
))
from
norm.students s
cross join lateral (
select
jsonb_agg(l.language) as languages
from
norm.students_languages l
where
s.id = l.student_id
) as lang
;
-- students + languages + grades
select
s.id,
jsonb_pretty(jsonb_build_object(
'id', s.id,
'name', s.name,
'address', jsonb_build_object(
'city', jsonb_build_object(
'name', s.addr_city,
'state', s.addr_state,
'country', s.addr_country
),
'street', s.addr_street,
'number', s.addr_number,
'geo', jsonb_build_object(
'latitude', s.addr_latitude,
'longitude', s.addr_longitude
)
),
'languages', lang.languages,
'grades', grds.grades
))
from
norm.students s
cross join lateral (
select
jsonb_agg(l.language) as languages
from
norm.students_languages l
where
s.id = l.student_id
) as lang
cross join lateral (
select
jsonb_agg(
jsonb_build_object(
'code', g.code,
'grade', g.grade
)
) as grades
from
norm.students_grades g
where
s.id = g.student_id
) as grds
;
Raw
sample-jsonpath.sql
SELECT * FROM students_big WHERE data @? '$ ? (@.name == "Fiddletown").languages[*] ? (@ like_regex "^pt")';
select * from students s where s.data @? '$.grades[*] ? (@.code == 10 && @.grade > 50)';
Raw
setup.sql
CREATE DATABASE pgconfbr22;
-- languages
DROP TABLE IF EXISTS pg_temp.sample_languages;
CREATE TEMP TABLE sample_languages AS
SELECT id, code
FROM
unnest('{pt_BR,pt_PT,en_US,en_AU,en_CA,en_GB,es_AR,es_CL,es_ES,es_MX}'::text[]) WITH ORDINALITY AS l(code, id);
ALTER TABLE sample_languages ADD PRIMARY KEY(id);
DROP FUNCTION IF EXISTS sample_languages();
CREATE FUNCTION sample_languages()
RETURNS text[]
LANGUAGE sql AS $$
SELECT array_agg(DISTINCT t.code)
FROM (
SELECT samp.code
FROM sample_languages samp
ORDER BY random()
LIMIT floor(random() * 5) + 1
) AS t
$$;
-- names
DROP TABLE IF EXISTS pg_temp.sample_names;
CREATE TEMP TABLE sample_names(id int generated always as identity primary key, name text);
\copy sample_names(name) from program 'grep "^[A-Z][a-z][a-z]" /usr/share/dict/words'
DROP FUNCTION IF EXISTS sample_name();
CREATE FUNCTION sample_name()
RETURNS text
LANGUAGE sql AS $$
SELECT samp.name AS value
FROM sample_names samp
WHERE samp.id = (SELECT floor(random() * (SELECT max(id) FROM sample_names))::int)
--ORDER BY random() LIMIT 1
$$;
-- grades
DROP FUNCTION IF EXISTS sample_grades();
CREATE FUNCTION sample_grades()
RETURNS jsonb
LANGUAGE sql AS $$
SELECT json_agg(g)
FROM (
SELECT
disc.code,
floor(random() * 100)::int AS grade
FROM
generate_series(1, 10) AS disc(code)
ORDER BY
random()
LIMIT
floor(random() * 10)
) g
$$;
DROP FUNCTION IF EXISTS sample_array(anyarray);
CREATE FUNCTION sample_array(vals anyarray)
RETURNS anyelement
LANGUAGE sql AS $$
SELECT
vals[floor(random() * cardinality(vals))::int + 1];
$$;
-- normalized tables
DROP SCHEMA norm CASCADE;
CREATE SCHEMA norm;
CREATE TABLE norm.students (
id uuid default gen_random_uuid() primary key,
name text,
addr_city text,
addr_state text,
addr_country text,
addr_street text,
addr_number text,
addr_complement text,
addr_latitude numeric,
addr_longitude numeric
);
INSERT INTO norm.students(name, addr_city, addr_state, addr_country, addr_street, addr_number, addr_latitude, addr_longitude)
SELECT
sample_name() || ' ' || sample_name(),
sample_array('{Foo,Bar,Baz,Zaz}'::text[]),
sample_array('{XX,YY,ZZ}'::text[]),
'BR',
'Rua ' || sample_name() || ' ' || sample_name(),
floor(random() * 1000),
random()*1900 - 999,
random()*1900 - 999
FROM
generate_series(1, 10)
;
CREATE TABLE norm.students_languages (
student_id uuid REFERENCES norm.students(id),
language text,
PRIMARY KEY(student_id, language)
);
INSERT INTO norm.students_languages
SELECT
s.id
, unnest(sample_languages())
FROM
norm.students s
;
CREATE TABLE norm.disciplines (
code integer generated always as identity primary key,
name text unique
);
INSERT INTO norm.disciplines(name) SELECT 'DISC ' || to_char(i, 'FM000') FROM generate_series(101, 110) i;
CREATE TABLE norm.students_grades (
student_id uuid REFERENCES norm.students(id),
code integer REFERENCES norm.disciplines(code),
grade numeric
);
INSERT INTO norm.students_grades
SELECT
t.id,
(g.grade->>'code')::int AS code,
(g.grade->>'grade')::numeric AS grade
FROM (
SELECT
s.id
, sample_grades() AS grades
FROM
norm.students s
) t
, jsonb_array_elements(t.grades) AS g(grade)
;
-- JSON table
DROP TABLE IF EXISTS students;
SELECT setseed(0);
--CREATE TABLE students AS
--SELECT
-- g.i,
-- jsonb_build_object(
-- 'id', gen_random_uuid(),
-- 'name', sample_name(),
-- 'languages', sample_languages(),
-- 'grades', sample_grades()
-- ) AS data
--FROM
-- generate_series(1, 10) AS g(i)
--;
CREATE TABLE students AS
select
s.id,
jsonb_build_object(
'id', s.id,
'name', s.name,
'address', jsonb_build_object(
'city', jsonb_build_object(
'name', s.addr_city,
'state', s.addr_state,
'country', s.addr_country
),
'street', s.addr_street,
'number', s.addr_number,
'geo', jsonb_build_object(
'latitude', s.addr_latitude,
'longitude', s.addr_longitude
)
),
'languages', lang.languages,
'grades', grds.grades
) as data
from
norm.students s
cross join lateral (
select
jsonb_agg(l.language) as languages
from
norm.students_languages l
where
s.id = l.student_id
) as lang
cross join lateral (
select
jsonb_agg(
jsonb_build_object(
'code', g.code,
'grade', g.grade
)
) as grades
from
norm.students_grades g
where
s.id = g.student_id
) as grds
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment