postgresql
Support JSON

Introduction

Création d'une table JSON pure

Pour créer une table JSON pure, vous devez fournir un seul champ avec le type JSONB :

CREATE TABLE mytable (data JSONB NOT NULL);

Vous devez également créer un index de base:

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

À ce stade, vous pouvez insérer des données dans la table et les interroger efficacement.

Interrogation de documents JSON complexes

Prenant un document JSON complexe dans une table:

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

Requête pour un élément de niveau supérieur:

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

Requête pour un élément simple dans un tableau:

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

Requête pour un objet dans un tableau:

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

Requête pour un objet imbriqué:

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

Performance de @> par rapport à -> et ->>

Il est important de comprendre la différence de performance entre l'utilisation de @> , -> et ->> dans la partie WHERE de la requête. Bien que ces deux requêtes semblent être globalement équivalentes:

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 première instruction utilisera l'index créé ci-dessus alors que les deux dernières ne le seront pas, nécessitant une analyse complète de la table.

Il est toujours possible d’utiliser l’opérateur -> lors de l’obtention des données résultantes, de sorte que les requêtes suivantes utiliseront également l’index:

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

Utilisation des opérateurs JSONb

Création d'une base de données et d'une table

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

Remplissage de la base de données

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

Permet de tout voir à l'intérieur des livres de table:

SELECT * FROM books;

Sortie:

-> opérateur renvoie des valeurs en dehors des colonnes JSON

Sélection d'une colonne:

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

Sortie:

Sélection de 2 colonnes:

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

Sortie:

-> vs ->>

L'opérateur -> renvoie le type JSON d'origine (qui peut être un objet), tandis que ->> renvoie le texte.

Retourne des objets NESTED

Vous pouvez utiliser le -> pour renvoyer un objet imbriqué et ainsi enchaîner les opérateurs:

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

Sortie:

Filtration

Sélectionnez des lignes en fonction d'une valeur dans votre JSON:

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

Notez que WHERE utilise -> donc nous devons comparer à JSON '"Dharma Bums"'

Ou nous pourrions utiliser ->> et comparer à 'Dharma Bums'

Sortie:

Filtrage imbriqué

Recherchez des lignes en fonction de la valeur d'un objet JSON imbriqué:

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

Sortie:

Un exemple du monde réel

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

Nous allons stocker les événements dans cette table, comme les pages vues. Chaque événement a des propriétés, qui peuvent être n'importe quoi (par exemple, la page en cours) et envoie également des informations sur le navigateur (comme le système d'exploitation, la résolution d'écran, etc.). Les deux sont totalement gratuits et pourraient changer avec le temps (car nous pensons à des choses supplémentaires à suivre).

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

Maintenant, permet de sélectionner tout:

SELECT * FROM events;

Sortie:

Opérateurs JSON + fonctions d'agrégation PostgreSQL

En utilisant les opérateurs JSON, combinés aux fonctions d'agrégation PostgreSQL traditionnelles, nous pouvons tirer ce que nous voulons. Vous avez la pleine puissance d'un SGBDR à votre disposition.

• Permet de voir l'utilisation du navigateur:

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

Sortie:

• Total des revenus par visiteur:

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

Sortie:

• Résolution d'écran moyenne

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

Sortie:

Plus d'exemples et de documentation ici et ici .