Managing Complex Data with JSON in PostgreSQL, part 1

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.

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

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"]'

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

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;

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

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'

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:

JSON-in-postgresql-p1-07.1.json

{
  "id": 0,
  "first_name": "George",
  "last_name": "Castanza",
  "characteristics": {
    "favorites": {
      "holidays": ["Summer of George"]
    }
  }
}

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

JSON-in-postgresql-p1-09.sql

SELECT jsonb_build_object('x', 'xylophone', 'y', 'yak');
-- Returns: { "x": "xylophone", "y": "yak" }

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" } }

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:

JSON-in-postgresql-p1-11.1.json

{
  "favorites": { "holidays": ["Summer of George"] },
  "full_name": "George Castanza"
}

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:
*/

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"
  }
]

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:
*/

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"
  }
}

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

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;

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

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

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"
    ]
  }
}

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"
    ]
  }
}