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

Question

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

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

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

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

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

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

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

• http://www.postgresql.org/docs/devel/static/datatype-json.html
• http://www.postgresql.org/docs/devel/static/functions-json.html

Обновить я

Я собрал суть, подробно описывающую, что в настоящее время возможно с 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

Answer 1

Postgres 9.2

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

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

Не мешает ему привести пример реализации в PLV8, который должен решить вашу проблему.

Postgres 9,3

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

• Руководство по новым функциям JSON.
• Postgres Wiki о новых функциях в pg 9.3.
• @Will разместил ссылку на блог с демонстрацией новых операторов в комментариях ниже.

Ответ на оригинальный вопрос в Postgres 9.3:

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

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

• Комбинации запросов с вложенным массивом записей в типе данных JSON

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

• Индекс для поиска элемента в массиве JSON

Postgres 9,4

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

• Руководство поjson а такжеjsonb типы данных и функции.
• Postgres Wiki на JSONB в pg 9.4

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

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

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

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

Postgres 9,5

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

• Основные хорошие новости в примечаниях к выпуску Postgres 9.5.

Answer 2

В 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/ для некоторых хороших примеров и учебника.

Answer 3

В 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

рельсы с

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)