Как выполнить запрос с использованием полей внутри нового типа данных PostgreSQL JSON?

Я ищу некоторые документы и / или примеры для новых функций JSON в PostgreSQL 9.2.

В частности, учитывая серию записей JSON:

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]

Как мне написать SQL, чтобы найти запись по имени?

В ванильном SQL:

SELECT * from json_data WHERE "name" = "Toby"

Официальное руководство разработчика довольно скудно:

Обновление I

Я составил суть, в которой подробно описаны возможности PostgreSQL 9.2 в настоящее время. Используя некоторые пользовательские функции, можно делать такие вещи, как:

SELECT id, json_string(data,'name') FROM things
WHERE json_string(data,'name') LIKE 'G%';

Обновление II

Теперь я переместил свои функции JSON в отдельный проект:

PostSQL - набор функций для преобразования PostgreSQL и PL / v8 в потрясающее хранилище документов JSON.


postgresql json sql postgresql-9.3 postgresql-9.2
person Toby Hede    schedule 12.05.2012    source источник
comment
Совсем недавно я нашел это сообщение в блоге Мэтта Шинкеля, в котором подробно объясняется запрос JSON в PostgreSQL schinckel.net/2014/05/25/querying-json-in-postgres   -  person knowbody    schedule 27.11.2014
comment
@knowbody Этот пост на самом деле посвящен запросам JSONB, который сильно отличается от JSON. Мне жаль, что я не уточнил это в посте.   -  person Matthew Schinckel    schedule 15.02.2016

Ответы (3)


arrow_upward
184
arrow_downward

Postgres 9.2

Я цитирую Эндрю Данстан из списка хакеров pgsql:

На каком-то этапе, возможно, будут некоторые функции обработки json (в отличие от функций создания json), но не в 9.2.

Это не мешает ему предоставить пример реализации в PLV8, который должен решить вашу проблему. (Ссылка сейчас мертва, вместо этого см. Современный PLV8.)

Postgres 9.3

Предлагает арсенал новых функций и операторов для добавления json-обработки.

Ответ на исходный вопрос в Postgres 9.3:

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ]'
  ) AS elem
WHERE elem->>'name' = 'Toby';

Расширенный пример:

Для больших таблиц вы можете добавить индекс выражения для повышения производительности:

Postgres 9.4

Добавляет jsonb (b для двоичного кода, значения хранятся как собственные типы Postgres) и еще больше функциональных возможностей для обоих типов. В дополнение к указанным выше индексам выражений jsonb также поддерживает GIN, btree и хеш-индексы, наиболее действенным из которых является GIN.

Руководство доходит до того, что предлагает:

В общем, большинству приложений следует предпочитать хранить данные JSON как jsonb, если только нет особых требований, таких как устаревшие предположения об упорядочивании ключей объектов.

Жирный акцент мой.

Повышение производительности за счет общих улучшений индексов GIN.

Postgres 9.5

Полные jsonb функции и операторы. Добавьте больше функций для управления jsonb на месте и для отображения.

person Erwin Brandstetter    schedule 12.05.2012
comment
Спасибо, я очень быстро столкнулся с проблемами типа, используя подход PLV8. Выглядит многообещающе, но на данный момент не совсем пригоден для использования. - person Toby Hede; 13.05.2012
comment
@TobyHede: Думаю, тогда нам придется ждать 9.3. - person Erwin Brandstetter; 14.05.2012
comment
@JoeShaw: Спасибо, я обновился соответствующим образом и добавил ссылку на Postgres Wiki. - person Erwin Brandstetter; 10.09.2013
comment
@ErwinBrandstetter, если я ищу WHERE elem - ›› 'right' = 'TRUE'; а JSON выглядит так: правильно: ИСТИНА, как правильно запрашивать логические термины? - person Shiraj; 19.01.2017
comment
@Shiraj: Пожалуйста, задавайте новые вопросы как вопрос. Комментарии не к месту. - person Erwin Brandstetter; 19.01.2017

arrow_upward
90
arrow_downward

В Postgres 9.3+ просто используйте оператор ->. Например,

SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram;

см. http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/, где есть несколько хороших примеров и руководство.

person Meekohi    schedule 20.03.2014
comment
В приведенном выше примере у вас должно быть поле с именем data с документом JSON: {images:{thumbnail:{url:'thumbnail.jpg'}}}. Сообщите нам, как выглядят ваши данные и какой запрос не выполняется. - person Meekohi; 27.03.2014
comment
stackoverflow.com/ questions / 22667401 / - person Mohamed El Mahallawy; 27.03.2014
comment
Как можно узнать, существует ли массив? Я вижу оператор # ››, но не понимаю, как им пользоваться! - person Mohamed El Mahallawy; 16.04.2014
comment
Могу ли я использовать подстановочный знак в этом запросе выбора? Т.е. SELECT data->'%'->'thumbnail'->'url' AS thumb FROM instagram; - person Bharat; 06.02.2018
comment
Ответ @ Meekohi работает хорошо: в частности, мне не нужен ::json, как описано в других сообщениях. Также обратите внимание, что оператор -> выдаст ошибку, если вы попытаетесь получить доступ к несуществующему свойству (т. Е. Если у вас смещен JSON): ERROR: column "jsonPropertyYouWant" does not exist - person The Red Pea; 25.11.2018

arrow_upward
19
arrow_downward

В postgres 9.3 используйте -> для доступа к объекту. 4 пример

seed.rb

se = SmartElement.new
se.data = 
{
    params:
    [
        {
            type: 1,
            code: 1,
            value: 2012,
            description: 'year of producction'
        },
        {
            type: 1,
            code: 2,
            value: 30,
            description: 'length'
        }
    ]
}

se.save

рельсы c

SELECT data->'params'->0 as data FROM smart_elements;

возвращается

                                 data
----------------------------------------------------------------------
 {"type":1,"code":1,"value":2012,"description":"year of producction"}
(1 row)

Вы можете продолжить вложение

SELECT data->'params'->0->'type' as data FROM smart_elements;

возвращение

 data
------
 1
(1 row)
person joseAndresGomezTovar    schedule 27.03.2014