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

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

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

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

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


postgresql json sql lateral postgresql-9.3
person Joe    schedule 24.10.2013    source источник

Ответы (4)


arrow_upward
56
arrow_downward

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

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

tbl - имя вашей таблицы, json_col - имя столбца JSON.

Подробнее в этом связанном ответе:

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

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

person Erwin Brandstetter    schedule 08.11.2013
comment
Это лучший ответ, который я когда-либо видел на вопросы такого типа. - person w0rp; 13.07.2016
comment
@ErwinBrandstetter Извините за оффтоп, но не могли бы вы помочь с этой проблемой: stackoverflow.com/q/49532773 (заранее спасибо !) - person Cepr0; 28.03.2018

arrow_upward
8
arrow_downward

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

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 ряд)

person adamc    schedule 30.01.2017
comment
как удалить адрес по id 123? - person Pranay Soni; 09.07.2017

arrow_upward
2
arrow_downward

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

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

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

json_array_elements(t.json_col->'emails')
person Deepak Mahakale    schedule 08.08.2016
comment
это супер супер полезно. Я изо всех сил пытался заставить его работать, и это просто прекрасно решает мою потребность. Спасибо! - person Mercutionario; 24.03.2017

arrow_upward
0
arrow_downward

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

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  ;
person Yunis Hawwash    schedule 12.05.2015
comment
Я пробовал это на Postgres 9.4.4, и это не работает: - person adamc; 30.01.2017