Postgres выбирает, где в массиве json содержится json

У меня есть таблица в базе данных postgresql 9.4 с полем jsonb. Некоторые примеры строк:

[{"name": "145119603", "numberOfCarrot": 2}]
[{"name": "1884595530", "numberOfCarrot": 1}]
[{"name": "363058213", "numberOfCarrot": 1}] 
[{"name": "1427965764", "numberOfCarrot": 1}]
[{"name": "193623800", "numberOfCarrot": 43}, {"name": "419955814", "numberOfCarrot": 0}]
[{"name": "624635532", "numberOfCarrot": 0}, {"name": "1884595530", "numberOfCarrot": 1}]
[{"name": "791712670", "numberOfCarrot": 1}]
[{"name": "895207852", "numberOfCarrot": 0}]
[{"name": "144695994", "numberOfCarrot": 3}, {"name": "384217055", "numberOfCarrot": 23}]
[{"name": "1079725696", "numberOfCarrot": 10}]
  • Как найти максимальное числоморков всей строки?
  • Как найти максимальное числоморков одной строки?
  • Как мне найти строку, в которой numberOfCarrot больше или меньше чего-то?
  • Как я могу найти элемент в массиве json, где numberOfCarrot превосходит или уступает чему-то?

postgresql json sql jsonb postgresql-9.4
person user3805731    schedule 24.05.2017    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Вам нужен уникальный столбец (обычно это первичный ключ, скажем, id), поэтому ваши данные должны выглядеть так:

(1, '[{"name": "145119603", "numberOfCarrot": 2}]'),
(2, '[{"name": "1884595530", "numberOfCarrot": 1}]'),
...

Используйте jsonb_array_elements() в боковом соединении. Примеры:

select max(value->>'numberOfCarrot')::int
from my_table,
jsonb_array_elements(jdata);

 max 
-----
  43
(1 row) 

select id, max((value->>'numberOfCarrot')::int)
from my_table,
jsonb_array_elements(jdata)
group by 1
order by 1;

 id | max 
----+-----
  1 |   2
  2 |   1
  3 |   1
  4 |   1
  5 |  43
  6 |   1
  7 |   1
  8 |   0
  9 |  23
 10 |  10
(10 rows)

Как использовать where:

select id, value->>'numberOfCarrot' as "numberOfCarrot"
from my_table,
jsonb_array_elements(jdata)
where (value->>'numberOfCarrot')::int > 20;

 id | numberOfCarrot 
----+----------------
  5 | 43
  9 | 23
(2 rows)

select id, array_agg(value->>'numberOfCarrot') as "numberOfCarrot"
from my_table,
jsonb_array_elements(jdata)
where (value->>'numberOfCarrot')::int > 2
group by 1
order by 1;

 id | numberOfCarrot 
----+----------------
  5 | {43}
  9 | {3,23}
 10 | {10}
(3 rows)
person klin    schedule 24.05.2017