Запрос для элемента массива в столбце JSON

Question

Недавно обновлен до использования PostgreSQL 9.3.1 для использования функций JSON. В моей таблице есть столбец типа json, который имеет такую ​​структуру:

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
} 

Это просто фиктивные данные для цели вопроса.

Можно ли запросить конкретный элемент в массиве электронных писем на основе идентификатора?
В значительной степени: "вернуть адрес электронной почты, где id=123)"?

Answer 1

Да, это возможно

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl быть вашим именем таблицы, json_col будучи именем столбца JSON.

Больше деталей в этом связанном ответе:

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

Подробнее о неявном CROSS JOIN LATERAL в последнем абзаце этого связанного ответа:

• PostgreSQL unnest () с номером элемента

Индекс для поддержки этого вида запроса:

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

Answer 2

В столбце JSONB в Postgres 9.4+ вы можете использовать оператор содержимого @> запросить элемент в массиве:

SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

См. Запрос для элементов массива внутри типа JSON для получения дополнительной информации.

Вот рабочий пример:

CREATE TABLE jsontest(data JSONB NOT NULL);
INSERT INTO jsontest VALUES (
  '{
     "name": "foo",
     "id": "123",
     "emails": 
     [
       {
         "address": "somethinghere",
         "id": "123"
       },
       {
         "address": "soemthing",
         "id": "456"
       }
     ]
  }'
);
SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

data
----
{"id": "123", "name": "foo", "emails": [{"id": "123", "address": "somethinghere"}, {"id": "456", "address": "soemthing"}]}

(1 ряд)

Answer 3

Наткнулся на этот пост и обнаружил, что вы можете напрямую запросить таблицу так:

SELECT *
FROM   table_name, json_array_elements(json_column) AS data
WHERE  data->>'id' = 123;

Опуская эту часть:

json_array_elements(t.json_col->'emails')

Answer 4

Вы можете сделать это так просто, как:

SELECT * FROM table WHERE emails->>'id' = '123';

кажется, вы храните идентификатор как строку, если это было целое число, вы можете сделать это следующим образом:

SELECT *  from table WHERE cast(emails->>'id' as integer ) = 123  ;

или вы можете получить все строки с id > 10

SELECT *  from table WHERE cast(emails->>'id' as integer ) > 10  ;