Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Managing Complex Data with JSON in PostgreSQL, part 1

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.

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"]}');
SELECT ('{"favorites":{"holidays":["Summer of George"]}}'::jsonb)->'favorites'->'holidays' AS holidays;
-- returns a row with a jsonb column with the contents: '["Summer of George"]'
SELECT (:FAVES::jsonb)->'favorites'->'holidays' AS holidays;
-- assumes you provide :FAVES as a value in the request template, and it contains stringified JSON
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;
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
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'
SELECT row_to_json(people.*) FROM people;
-- One row for each person, each with a single json column like this:
{
"id": 0,
"first_name": "George",
"last_name": "Castanza",
"characteristics": {
"favorites": {
"holidays": ["Summer of George"]
}
}
}
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
SELECT jsonb_build_object('x', 'xylophone', 'y', 'yak');
-- Returns: { "x": "xylophone", "y": "yak" }
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" } }
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:
{
"favorites": { "holidays": ["Summer of George"] },
"full_name": "George Castanza"
}
SELECT jsonb_agg(
jsonb_build_object(
'id', people.id,
'full_name', concat_ws(' ', first_name, last_name)
)
)
FROM people;
/*
Returns:
*/
[
{
"id": "9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4",
"full_name": "George Castanza"
},
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"full_name": "Cosmo Kramer"
}
]
SELECT jsonb_object_agg(
id,
jsonb_build_object(
'full_name', concat_ws(' ', first_name, last_name)
)
)
FROM people;
/*
Returns:
*/
{
"6ae1da27-0565-4649-96c5-e4c1da48de21": {
"full_name": "Cosmo Kramer"
},
"9eda3717-ee4a-4ac7-ba2e-d3b43b7e79c4": {
"full_name": "George Castanza"
}
}
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)
);
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;
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();
UPDATE people
SET characteristics = (
characteristics || jsonb_build_object(
'inventions', characteristics->'inventions' || '["The Beach"]'::jsonb
)
)
WHERE id = '6ae1da27-0565-4649-96c5-e4c1da48de21';
{
"id": "6ae1da27-0565-4649-96c5-e4c1da48de21",
"last_name": "Kramer",
"first_name": "Cosmo",
"characteristics": {
"inventions": [
"Coffee Table Book",
"The Bro"
]
}
}
{
"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