postgresql
Soporte JSON

Introducción

Creando una tabla JSON pura

Para crear una tabla JSON pura, debe proporcionar un único campo con el tipo JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

También debe crear un índice básico:

CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

En este punto, puede insertar datos en la tabla y consultarlos de manera eficiente.

Consultar documentos JSON complejos.

Tomando un documento JSON complejo en una tabla:

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

Consulta por un elemento de nivel superior:

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

Consulta por un elemento simple en una matriz:

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

Consulta de un objeto en una matriz:

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

Consulta de un objeto anidado:

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

Rendimiento de @> comparado con -> y ->>

Es importante comprender la diferencia de rendimiento entre usar @> , -> y ->> en la parte WHERE de la consulta. Aunque estas dos consultas parecen ser ampliamente equivalentes:

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

la primera declaración utilizará el índice creado anteriormente, mientras que las dos últimas no lo harán, lo que requerirá una exploración completa de la tabla.

Todavía es posible usar el operador -> al obtener datos resultantes, por lo que las siguientes consultas también usarán el índice:

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';

Usando operadores JSONb

Creando un DB y una tabla

DROP DATABASE IF EXISTS books_db;
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

Poblando el DB

INSERT INTO books(client, data) values (
    'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),(
    'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),(
    'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

Veamos todo dentro de los libros de mesa:

SELECT * FROM books;

Salida:

-> operador devuelve valores fuera de columnas JSON

Seleccionando 1 columna:

SELECT client, 
    data->'title' AS title
    FROM books;

Salida:

Seleccionando 2 columnas:

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;

Salida:

-> vs ->>

El operador -> devuelve el tipo JSON original (que podría ser un objeto), mientras que ->> devuelve texto.

Devuelve objetos anidados

Puede usar -> para devolver un objeto anidado y así encadenar los operadores:

SELECT client, 
   data->'author'->'last_name' AS author
   FROM books;

Salida:

Filtración

Seleccione filas basadas en un valor dentro de su JSON:

 SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'title' = '"Dharma Bums"';

Observe DÓNDE usa -> así que debemos comparar con JSON '"Dharma Bums"'

O podríamos usar ->> y comparar con 'Dharma Bums'

Salida:

Filtrado anidado

Encuentre filas basadas en el valor de un objeto JSON anidado:

SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'author'->>'last_name' = 'Kerouac';

Salida:

Un ejemplo del mundo real.

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

Vamos a almacenar eventos en esta tabla, como páginas vistas. Cada evento tiene propiedades, que pueden ser cualquier cosa (por ejemplo, la página actual) y también envía información sobre el navegador (como el sistema operativo, la resolución de la pantalla, etc.). Ambos son completamente libres y podrían cambiar con el tiempo (ya que pensamos en cosas adicionales para rastrear).

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

Ahora vamos a seleccionar todo:

SELECT * FROM events;

Salida:

Operadores JSON + funciones agregadas de PostgreSQL

Usando los operadores JSON, combinados con las funciones agregadas tradicionales de PostgreSQL, podemos sacar lo que queramos. Tienes toda la potencia de un RDBMS a tu disposición.

• Permite ver el uso del navegador:

    SELECT browser->>'name' AS browser, 
      count(browser)
      FROM events
      GROUP BY browser->>'name';
  

Salida:

• Ingresos totales por visitante:

    SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
    FROM events
    WHERE CAST(properties->>'amount' AS integer) > 0
    GROUP BY visitor_id;
  

Salida:

• Resolución media de la pantalla

    SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
      AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
    FROM events;
  

Salida:

Más ejemplos y documentación aquí y aquí .