Запросы к данным JSON в Postgres

Введение в NoSQL

Jake Roach

Data Engineer

Запросы к данным JSON в Postgres

Две таблицы: одна содержит единственный столбец типа JSON, другая — результат запроса, извлекающего данные из столбца parent_meta.

-> оператор

Принимает ключ, возвращает поле как JSON

->> оператор

Принимает ключ, возвращает поле как текст

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

Запросы к вложенным объектам JSON

Запросы к вложенным объектам JSON:

Используйте -> и ->> совместно
Сначала получите вложенный объект
Затем извлеките нужное поле

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

Запросы к массивам JSON

Доступ к элементам массива JSON:

Передайте INT в -> — возвращает поле как JSON
Передайте INT в ->> — возвращает поле как текст

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

Определение типа данных в объектах JSON

Две таблицы: одна содержит единственный столбец типа JSON, другая — тип объекта JSON в отдельном столбце.

Функция json_typeof

Возвращает тип самого внешнего объекта
Используется с ->
Полезна для отладки и построения запросов
Как правило, не используется с ->> оператором

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

Обзор

SELECT
    -- Top-level fields
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- Nested fields
    <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>,

    -- Type of
    json_typeof(<column-name> -> <field-name>) AS <alias>
FROM <table-name>;

Давайте потренируемся!

Введение в NoSQL