PGConf.Brasil 2022 - JSON on PostgreSQL, from zero to hero

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")';

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
;

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)';

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
;