Consultando dados JSON com Postgres

Introdução a NoSQL

Jake Roach

Data Engineer

Consultando dados JSON com Postgres

Duas tabelas: uma com uma coluna do tipo JSON e outra com o resultado da consulta extraindo informações da coluna parent_meta.

-> operator

Recebe uma chave, retorna o campo como JSON

->> operator

Recebe uma chave, retorna o campo como texto

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

Consultando objetos JSON aninhados

Duas tabelas: uma com uma coluna do tipo JSON e outra com o resultado da consulta extraindo informações da coluna parent_meta.

Para consultar objetos JSON aninhados:

Use -> e ->> em conjunto
Primeiro, retorne o objeto aninhado
Depois, extraia o campo

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

Consultando arrays JSON

Duas tabelas: uma com uma coluna do tipo JSON e outra com o resultado da consulta extraindo informações da coluna parent_meta.

Acessando elementos de arrays JSON:

Passe um INT para ->, retorna o campo como JSON
Passe um INT para ->>, retorna o campo como texto

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

Identificando o tipo de dado em objetos JSON

Duas tabelas: uma com uma coluna única com o tipo do objeto JSON de saída.

json_typeof function

Retorna o tipo do objeto mais externo
Use com ->
Útil para depurar e montar consultas
Normalmente não usado com o operador ->>

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

Revisão

SELECT
    -- Campos de nível superior
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- Campos aninhados
    <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 praticar!

Introdução a NoSQL