Skip to content

Instantly share code, notes, and snippets.

@giseburt

giseburt/JSON-in-postgresql-p1-00.md

Last active May 11, 2021 18:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save giseburt/2cee8334838abf5e2c13a1c6dd86e440 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save giseburt/2cee8334838abf5e2c13a1c6dd86e440 to your computer and use it in GitHub Desktop.
Download ZIP
Managing Complex Data with JSON in PostgreSQL, part 1
Raw
JSON-in-postgresql-p1-00.md

Managing Complex Data with JSON in PostgreSQL, Part 1

This is the support code for a blog post about using JSON in PostgreSQL.

Read the full article here.

Raw
JSON-in-postgresql-p1-01.sql
CREATE TABLE people (
first_name text,
last_name text,
characteristics jsonb,
id uuid default gen_random_uuid(),
PRIMARY KEY (id)
);
INSERT INTO people VALUES
('George', 'Castanza', '{"favorites":{"holidays":["Summer of George"]}}'),
('Cosmo', 'Kramer', '{"inventions": ["Coffee Table Book", "The Bro"]}');
Raw
JSON-in-postgresql-p1-02.sql
SELECT ('{"favorites":{"holidays":["Summer of George"]}}'::jsonb)->'favorites'->'holidays' AS holidays;
-- returns a row with a jsonb column with the contents: '["Summer of George"]'
Raw
JSON-in-postgresql-p1-03.sql
SELECT (:FAVES::jsonb)->'favorites'->'holidays' AS holidays;
-- assumes you provide :FAVES as a value in the request template, and it contains stringified JSON
Raw
JSON-in-postgresql-p1-04.sql
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO people
SELECT *
FROM json_populate_recordset (NULL::people, '
[
{
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
"last_name": "Castanza",
"first_name": "George",
"characteristics": {
"favorites": {
"holidays": [
"Summer of George"
]
},
"enemies": [
{
"id": "5d7994d2-7960-4d73-9e1d-b4c255703765",
"flavor": "Unexplained"
},
{
"id": "a4adc3fd-485f-4fc7-a561-863ea78e9bb9",
"flavor": "Teacher"
}
]
}
},
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"last_name": "Kramer",
"first_name": "Cosmo",
"characteristics": {
"inventions": [
"Coffee Table Book",
"The Bro"
]
}
},
{
"id": "a4adc3fd-485f-4fc7-a561-863ea78e9bb9",
"last_name": "Mr.",
"first_name": "Heyman",
"characteristics": {
"occupation": "Gym Teacher (Fired)"
}
},
{
"id": "5d7994d2-7960-4d73-9e1d-b4c255703765",
"last_name": "Joe",
"first_name": "Davola",
"characteristics": {
"enemies":[
{
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
"flavor": "Occupational Rivalry"
}
]
}
}
]');
COMMIT;
Raw
JSON-in-postgresql-p1-05.py
import psycopg2
from psycopg2 import sql
from psycopg2.extras import Json as pgJson
conn = psycopg2.connect(""" ... """)
cur = conn.cursor()
# table_name is a string of the table name to import into
# json_data is an list of dicts (array of objects, in JSON-speak) of the data to import
try:
cur.execute(sql.SQL("insert into {0} SELECT * FROM json_populate_recordset (NULL::{0}, %s)").format(
sql.Identifier(table_name)), [pgJson(json_data)])
conn.commit()
except:
conn.rollback()
# Handle the error
Raw
JSON-in-postgresql-p1-06.sql
SELECT
-- typical list of columns to return
p.first_name, enemy.id, enemy.first_name, enemy.characteristics
FROM
people p,
-- get the enemies info from the `characteristics` column,
-- treating it like a table on it's own
-- with jsonb_array_elements
jsonb_array_elements(p.characteristics->'enemies') enemy_info
-- typical left join, also searching the "people" table
-- note: getting the 'id' value as string then casting to uuid
LEFT JOIN people enemy ON (enemy_info->>'id')::uuid = enemy.id
WHERE
p.id = ?; -- assuming ? is provided by the application, properly escaped
-- output is a list of rows with the requested columns,
-- including one jsonb column 'enemy.characteristics'
Raw
JSON-in-postgresql-p1-07.0.sql
SELECT row_to_json(people.*) FROM people;
-- One row for each person, each with a single json column like this:
Raw
JSON-in-postgresql-p1-07.1.json
{
"id": 0,
"first_name": "George",
"last_name": "Castanza",
"characteristics": {
"favorites": {
"holidays": ["Summer of George"]
}
}
}
Raw
JSON-in-postgresql-p1-08.sql
SELECT row_to_json(named.*)
FROM
(
SELECT
people.*,
concat_ws(' ', first_name, last_name) AS full_name
FROM people
) AS named;
-- Returns the same as the prvious query, but with a new full_name key
Raw
JSON-in-postgresql-p1-09.sql
SELECT jsonb_build_object('x', 'xylophone', 'y', 'yak');
-- Returns: { "x": "xylophone", "y": "yak" }
Raw
JSON-in-postgresql-p1-10.sql
SELECT jsonb_build_object(
'x', 'xylophone',
'name', jsonb_build_object('f', first_name, 'l', last_name)
) FROM people;
-- Returns rows like: { "x": "xylophone", "name": { "f": "George", "l": "Castanza" } }
Raw
JSON-in-postgresql-p1-11.0.sql
SELECT people.characteristics || jsonb_build_object(
'full_name',
concat_ws(' ', first_name, last_name)
)
FROM people;
-- Returns one row for each person, each with a single json column like this:
Raw
JSON-in-postgresql-p1-11.1.json
{
"favorites": { "holidays": ["Summer of George"] },
"full_name": "George Castanza"
}
Raw
JSON-in-postgresql-p1-12.0.sql
SELECT jsonb_agg(
jsonb_build_object(
'id', people.id,
'full_name', concat_ws(' ', first_name, last_name)
)
)
FROM people;
/*
Returns:
*/
Raw
JSON-in-postgresql-p1-12.1.json
[
{
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
"full_name": "George Castanza"
},
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"full_name": "Cosmo Kramer"
}
]
Raw
JSON-in-postgresql-p1-12.2.sql
SELECT jsonb_object_agg(
id,
jsonb_build_object(
'full_name', concat_ws(' ', first_name, last_name)
)
)
FROM people;
/*
Returns:
*/
Raw
JSON-in-postgresql-p1-12.3.json
{
"6ae1da27-0565-4649-96c5-e4c1da48de21": {
"full_name": "Cosmo Kramer"
},
"9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4": {
"full_name": "George Castanza"
}
}
Raw
JSON-in-postgresql-p1-13.sql
CREATE TABLE change_log (
id uuid default gen_random_uuid(),
old_value jsonb,
new_value jsonb,
operation text,
table_name text,
created_timestamp timestamp not null default now(),
PRIMARY KEY (id)
);
Raw
JSON-in-postgresql-p1-14.sql
CREATE OR REPLACE FUNCTION change_log_fn() RETURNS TRIGGER AS $$
DECLARE
new_log_uuid uuid;
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO change_log (id, old_value, operation, table_name)
VALUES (
DEFAULT,
row_to_json(OLD),
TG_OP,
TG_TABLE_NAME
) returning id into new_log_uuid;
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO change_log (id, new_value, operation, table_name)
VALUES (
DEFAULT,
row_to_json(NEW),
TG_OP,
TG_TABLE_NAME
) returning id into new_log_uuid;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE' and (OLD != NEW)) THEN
INSERT INTO change_log (id, old_value, new_value, operation, table_name)
VALUES (
DEFAULT,
row_to_json(OLD),
row_to_json(NEW),
TG_OP,
TG_TABLE_NAME
) returning id into new_log_uuid;
RETURN NEW;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Raw
JSON-in-postgresql-p1-15.sql
DROP TRIGGER IF EXISTS people_change_tg ON people;
CREATE TRIGGER people_change_tg
AFTER INSERT OR UPDATE OR DELETE
ON people
FOR EACH ROW EXECUTE PROCEDURE change_log_fn();
Raw
JSON-in-postgresql-p1-16.sql
UPDATE people
SET characteristics = (
characteristics || jsonb_build_object(
'inventions', characteristics->'inventions' || '["The Beach"]'::jsonb
)
)
WHERE id = '6ae1da27-0565-4649-96c5-e4c1da48de21';
Raw
JSON-in-postgresql-p1-17.0.json
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"last_name": "Kramer",
"first_name": "Cosmo",
"characteristics": {
"inventions": [
"Coffee Table Book",
"The Bro"
]
}
}
Raw
JSON-in-postgresql-p1-17.1.json
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"last_name": "Kramer",
"first_name": "Cosmo",
"characteristics": {
"inventions": [
"Coffee Table Book",
"The Bro",
"The Beach"
]
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment