Interrogare dati JSON con Postgres

Introduzione ai database NoSQL

Jake Roach

Data Engineer

Interrogare dati JSON con Postgres

Due tabelle: una con una colonna di tipo JSON, l'altra con il risultato della query che estrae informazioni dalla colonna parent_meta.

-> operatore

Prende una chiave, restituisce il campo come JSON

->> operatore

Prende una chiave, restituisce il campo come testo

SELECT
    parent_meta -> 'guardian' AS guardian
    parent_meta ->> 'status' AS status
FROM student;

Interrogare oggetti JSON annidati

Due tabelle: una con una colonna di tipo JSON, l'altra con il risultato della query che estrae informazioni dalla colonna parent_meta.

Per interrogare oggetti JSON annidati:

Usa -> e ->> insieme
Prima restituisci l'oggetto annidato
Poi estrai il campo

SELECT
    parent_meta -> 'jobs' ->> 'P1' AS jobs_P1,
    parent_meta -> 'jobs' ->> 'P2' AS jobs_P2
FROM student;

Interrogare array JSON

Due tabelle: una con una colonna di tipo JSON, l'altra con il risultato della query che estrae informazioni dalla colonna parent_meta.

Accedere agli elementi di un array JSON:

Passa un INT a ->, restituisce il campo come JSON
Passa un INT a ->>, restituisce il campo come testo

SELECT
    parent_meta -> 'educations' ->> 0
    parent_meta -> 'educations' ->> 1
FROM student;

Trovare il tipo di dati memorizzati in oggetti JSON

Due tabelle: una con una colonna di tipo JSON, l'altra con il tipo dell'oggetto JSON in output in una singola colonna.

Funzione json_typeof

Restituisce il tipo dell'oggetto più esterno
Usare con ->
Utile per debug e per costruire query
Di solito non si usa con l'operatore ->>

SELECT
    json_typeof(parent_meta -> 'jobs')
FROM students;

Riepilogo

SELECT
    -- Campi di primo livello
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- Campi annidati
    <column-name> -> '<parent-field-name>' ->> '<nested-field-name>' AS <alias>,

    -- Array
    <column-name> -> '<parent-field-name>' -> 0 AS <alias>,
    <column-name> -> '<parent-field-name>' ->> 1 AS <alias>,

    -- Tipo di
    json_typeof(<column-name> -> <field-name>) AS <alias>
FROM <table-name>;

Esercitiamoci!

Introduzione ai database NoSQL