Consultar datos JSON con Postgres

Introducción a NoSQL

Jake Roach

Data Engineer

Consultar datos JSON con Postgres

Dos tablas: una con una única columna de tipo JSON y otra con el resultado de la consulta que extrae información de la columna parent_meta.

Operador ->

Recibe una clave y devuelve el campo como JSON

Operador ->>

Recibe una clave y devuelve el campo como texto

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

Consultar objetos JSON anidados

Dos tablas: una con una única columna de tipo JSON y otra con el resultado de la consulta que extrae información de la columna parent_meta.

Para consultar objetos JSON anidados:

Usa -> y ->> juntos
Primero, devuelve el objeto anidado
Luego, extrae el campo

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

Consultar arrays JSON

Dos tablas: una con una única columna de tipo JSON y otra con el resultado de la consulta que extrae información de la columna parent_meta.

Acceder a elementos de arrays JSON:

Pasa un INT a ->, devuelve el campo como JSON
Pasa un INT a ->>, devuelve el campo como texto

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

Encontrar el tipo de datos almacenado en objetos JSON

Dos tablas: una con una única columna de tipo JSON y otra con el tipo del objeto JSON en una sola columna.

Función json_typeof

Devuelve el tipo del objeto más externo
Úsala con ->
Útil para depurar y crear consultas
Normalmente no se usa con el operador ->>

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

Repaso

SELECT
    -- Campos de nivel superior
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- Campos anidados
    <column-name> -> '<parent-field-name>' ->> '<nested-field-name>' AS <alias>,

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

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

¡Vamos a practicar!

Introducción a NoSQL