Skip to main content

Запрос данных из пакетов JSON в поле таблицы

Для хранения пакетов используем такую таблицу:

upload_data.png

Пакеты хранятся в поле pack_data, они одинаковой структуры для определенного типа записи (поле type_id)

Пакет имеет такую структуру:

[
  {"charsetinfo": 
    {"name": "utf-8"}
  },
  {"history": 
    [
      {"BOARDID": "TQBR", "TRADEDATE": "2026-09-21", "SHORTNAME": "РоссЮг ао", "SECID": "MRKY", "NUMTRADES": 275, "VALUE": 1541379, "OPEN": 0.07065, "LOW": 0.0695, "HIGH": 0.0716, "LEGALCLOSEPRICE": 0.07015, "WAPRICE": 0.0704, "CLOSE": 0.07055, "VOLUME": 21890000, "MARKETPRICE2": 0.0702, "MARKETPRICE3": 0.0702, "ADMITTEDQUOTE": null, "MP2VALTRD": 766668, "MARKETPRICE3TRADESVALUE": 766668, "ADMITTEDVALUE": null, "WAVAL": 0, "TRADINGSESSION": 3, "CURRENCYID": "SUR", "TRENDCLSPR": 0.21, "TRADE_SESSION_DATE": "2026-09-21"},{"BOARDID": "TQBR", "TRADEDATE": "2026-09-21", "SHORTNAME": "Мостотрест", "SECID": "MSTT", "NUMTRADES": 162, "VALUE": 1539552, "OPEN": 69.4, "LOW": 67.9, "HIGH": 69.4, "LEGALCLOSEPRICE": 67.9, "WAPRICE": 68.5, "CLOSE": 68.35, "VOLUME": 22470, "MARKETPRICE2": 68.45, "MARKETPRICE3": 68.45, "ADMITTEDQUOTE": null, "MP2VALTRD": 1292162.5, "MARKETPRICE3TRADESVALUE": 1292162.5, "ADMITTEDVALUE": null, "WAVAL": 0, "TRADINGSESSION": 3, "CURRENCYID": "SUR", "TRENDCLSPR": -1.51, "TRADE_SESSION_DATE": "2026-09-21"},
        ...
     ]
  }
]

Нам нужно соединить все пакеты и получить записи из поля "history".

Чтобы развернуть массив из массива history во всех JSON-пакетах одной SQL-выборкой, в PostgreSQL используются функции для работы с JSON/JSONB:

  1. Приведение типа: Так как поле pack_data имеет тип text, его нужно привести к jsonb (или json): pack_data::jsonb.

  2. jsonb_array_elements: Позволяет «расплющить» (распаковать) элементы JSON-массива в отдельные строки.

SQL-запрос

Поскольку yаш JSON представляет собой массив объектов, где массив history находится во втором элементе (pack_data::jsonb -> 1 -> 'history'), запрос будет выглядеть так:

SQL 
SELECT u.upload_id,
       u.pack_name,
       u.upload_time,
       -- Извлекаем поля из объектов внутри history
       history_item ->> 'SECID' AS secid,
       history_item ->> 'SHORTNAME' AS shortname,
       history_item ->> 'TRADEDATE' AS tradedate,
       (history_item ->> 'OPEN')::numeric AS open_price,
       (history_item ->> 'CLOSE')::numeric AS close_price,
       (history_item ->> 'VOLUME')::bigint AS volume,
       (history_item ->> 'VALUE')::numeric AS value,
       history_item ->> 'BOARDID' AS boardid
  FROM trading.upload_data u
 WHERE u.type_id = 1
 -- 1. Извлекаем массив history из второго элемента корня JSON и разворачиваем его по строкам
 CROSS JOIN LATERAL jsonb_array_elements(u.pack_data::jsonb -> 1 -> 'history') AS history_item;

Как это работает:

  1. u.pack_data::jsonb -> 1 — берет второй элемент корневого JSON-массива (индексация с 0, поэтому 1 указывает на объект {"history": [...]}).

  2. -> 'history' — переходит непосредственно к массиву цен и сделок.

  3. jsonb_array_elements(...) — превращает каждый элемент массива history в отдельную строку результата.

  4. CROSS JOIN LATERAL — связывает каждую строку из таблицы upload_data с каждым распакованным элементами из массива history.

  5. history_item ->> 'FIELD_NAME' — извлекает текстовое значение конкретного ключа из JSON-объекта.

  6. ::numeric, ::bigint — сразу приводит текстовые значения JSON к правильным математическим типам PostgreSQL для последующей фильтрации или агрегации.

Альтернатива: Если порядок элементов в корневом JSON может меняться

Если элемент history не всегда гарантированно идет вторым (с индексом 1), безопаснее сначала распаковать корневой массив, а затем найти элемент с ключом history:

SQL 
SELECT u.upload_id,
       history_item ->> 'SECID' AS secid,
       history_item ->> 'SHORTNAME' AS shortname,
       (history_item ->> 'CLOSE')::numeric AS close_price
  FROM trading.upload_data u
 WHERE u.type_id = 1
 -- Распаковываем корневой массив
 CROSS JOIN LATERAL jsonb_array_elements(u.pack_data::jsonb) AS root_elem
 -- Распаковываем массив history (игнорируя элементы без этого ключа, например charsetinfo)
 CROSS JOIN LATERAL jsonb_array_elements(root_elem -> 'history') AS history_item;

Этот вариант гибче, так как найдет массив history в любом месте структуры пакета.

Что делает оператор:  ->>

Оператор ->> в PostgreSQL используется для извлечения значения из JSON или JSONB в виде обычного текста (text).

В PostgreSQL есть два похожих оператора для работы с JSON, и разница между ними принципиальна:

Оператор Что делает Возвращаемый тип Назначение
-> Извлекает поле или элемент массива json или jsonb Для дальнейшего навигационного продвижения вглубь структуры JSON
->> Извлекает поле или элемент массива text Для вывода конечных данных, фильтрации в WHERE или приведения типов

Примеры применения

Предположим, у вас есть переменная или колонка data с содержимым {"user": {"name": "Иван", "age": 25}}.

1. Извлечение поля по ключу
SQL 
-- Оператор -> возвращает JSON ("Иван" в кавычках)
SELECT data -> 'user' -> 'name'; 
-- Результат: "Иван" (тип jsonb)

-- Оператор ->> возвращает чистый текст (без кавычек)
SELECT data -> 'user' ->> 'name'; 
-- Результат: Иван (тип text)
2. Извлечение элемента из JSON-массива по индексу
Если в колонке находится массив ["apple", "banana", "cherry"]:

SQL 
SELECT data ->> 0; 
-- Результат: apple (тип text)
3. Приведение типов для расчетов и фильтрации
Поскольку ->> возвращает обычный text, результат можно легко привести к числу, дате или логическому типу через ::type:

SQL 
-- Извлекаем возраст как число для условия в WHERE
SELECT * 
FROM users_table 
WHERE (data ->> 'age')::int >= 18;

-- Извлекаем дату и делаем сортировку
SELECT data ->> 'TRADEDATE' AS trade_date
FROM trading.upload_data
ORDER BY (data ->> 'TRADEDATE')::date DESC;

Чек-лист для запоминания

  • Если вы хотите продолжить цепочку погружения в JSON дальше — используйте одиночную стрелку: data -> 'a' -> 'b'.

  • На самом последнем шаге, когда нужно получить конечное значение в виде текста для вывода, используйте двойную стрелку: data -> 'a' ->> 'b'.

No comments to display

Back to top