ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Условие WHERE внутри JSON поля postgresql?
8 сообщений из 8, страница 1 из 1
Условие WHERE внутри JSON поля postgresql?
    #39641582
manking
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
manking
Участник
В базе есть записи

id : 3024
data : ["Егор","[{\"text\":\" Центральноафриканская Республика CAF \",\"value\":\"2696\"}]"]

id : 3025
data : ["Олег","[{\"text\":\" Египет \",\"value\":\"3341\"}]"]


Как сделать запрос, чтобы выбрать только записи со значением value
2696 (Центральноафриканская Республика)
которые находятся внутри JSON записи поля data?
...
Рейтинг: 0 / 0
07.05.2018, 17:01
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641586
Alex URS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex URS
Гость
manking,


Код: plsql
1.
2.
3.
4.
5.
6.
7.
 SELECT 
	id,
	json_data.*
FROM 
	table,      jsonb_each(table.data::jsonb) AS json_data
WHERE value = 'value'
AND key = 2696
...
Рейтинг: 0 / 0
07.05.2018, 17:07
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641599
manking
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
manking
Участник
Alex URSmanking,


Код: plsql
1.
2.
3.
4.
5.
6.
7.
 SELECT 
	id,
	json_data.*
FROM 
	table,      jsonb_each(table.data::jsonb) AS json_data
WHERE value = 'value'
AND key = 2696



Пишет
Ошибка SQL:

ОШИБКА: неверный синтаксис для типа json
LINE 6: WHERE value = 'value'
^
DETAIL: Ошибочный элемент текста "value".
CONTEXT: данные JSON, строка 1: value
В операторе:

SELECT
id,
json_data.*
FROM
directories_data , jsonb_each(directories_data.data::jsonb) AS json_data
WHERE value = 'value'
AND key = 2696
...
Рейтинг: 0 / 0
07.05.2018, 17:16
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641649
Alex URS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex URS
Гость
manking,

думал догадаешься...
у тебя же json :[array json]

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WITH tbl AS (
select 3024::integer as id , json_build_object('data', json_build_array('Егор', json_build_object('text','Центральноафриканская Республика CAF','value',2696))) as dt
union all
select 3025::integer as id ,json_build_object('data', json_build_array('Олег', json_build_object('text','Египед','value',3341))) as dt
 ) SELECT id, dt
          FROM tbl, json_each(tbl.dt::json) AS json_data,
          LATERAL json_array_elements(
          json_data.value
          ) segment
          WHERE segment ->>'value' = '2696'
...
Рейтинг: 0 / 0
07.05.2018, 19:06
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641654
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
mankingВ базе есть записи

id : 3024
data : ["Егор","[{\"text\":\" Центральноафриканская Республика CAF \",\"value\":\"2696\"}]"]

id : 3025
data : ["Олег","[{\"text\":\" Египет \",\"value\":\"3341\"}]"]


Как сделать запрос, чтобы выбрать только записи со значением value
2696 (Центральноафриканская Республика)
которые находятся внутри JSON записи поля data?
https://github.com/postgrespro/jsquery/ - модуль позволяет SQL искать ВНУТРИ JSONB
без него будете строчить запросы на весь экран
а с ним просто:
Код: sql
1.
SELECT * FROM js_test WHERE value @@ '*.color = "red"'; -- найти что-нибудь красненькое


обычный jsonb не умеет дальше ключа эффективно искать
...
Рейтинг: 0 / 0
07.05.2018, 19:35
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641655
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
но вообще разрабы советуют отдельные колонки делать, если нужен поиск по ним
...
Рейтинг: 0 / 0
07.05.2018, 19:36
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641664
Alex URS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex URS
Гость
mankingобычный jsonb не умеет дальше ключа эффективно искать

можно мне по подробнее, что Вы имеете в виду?

PS за jsquery спасибо
...
Рейтинг: 0 / 0
07.05.2018, 19:58
| Ответить | Цитировать | Написать  
Условие WHERE внутри JSON поля postgresql?
    #39641714
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
GIN
умеет: @>, ?, ?& and ?|
запрос должен быть по верхним ключам дерева
Can query nested objects, but only in paths rooted at the top level .
в вашем случае это: id, data

авторThe non-default GIN operator class jsonb_path_ops supports indexing the @> operator only. An example of creating an index with this operator class is:
# !!! такой индекс умеет только @>, но он быстрее и меньше, чем стандартный jsonb_ops
Код: sql
1.
CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);


Рассмотрим пример таблицы, в которой хранятся документы JSON, получаемые от сторонней веб-службы, с документированным определением схемы. Типичный документ:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}


Мы сохраняем эти документы в таблице api, в столбце jdoc типа jsonb. Если по этому столбцу создаётся GIN-индекс, он может применяться в подобных запросах:
-- Найти документы, в которых ключ "company" имеет значение "Magnafone"
Код: sql
1.
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';



Однако, в следующих запросах он не будет использоваться, потому что, несмотря на то, что оператор ? — индексируемый, он применяется не к индексированному столбцу jdoc непосредственно:
-- Найти документы, в которых ключ "tags" содержит ключ или элемент массива "qui"
Код: sql
1.
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';



И всё же, правильно применяя индексы выражений, в этом запросе можно задействовать индекс. Если запрос определённых элементов в ключе "tags" выполняется часто, вероятно стоит определить.
такой индекс:
Код: sql
1.
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));


-- Теперь предложение WHERE jdoc -> 'tags' ? 'qui' будет выполняться как применение индексируемого оператора ? к индексируемому выражению jdoc -> 'tags'.

Ещё один подход к использованию проверок на существование:
-- Найти документы, в которых ключ "tags" содержит элемент массива "qui"
Код: sql
1.
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';



Этот запрос может задействовать простой GIN-индекс по столбцу jdoc. Но заметьте, что такой индекс будет хранить копии всех ключей и значений в поле jdoc, тогда как индекс выражения из.
предыдущего примера хранит только данные внутри объекта с ключом tags. Хотя подход с простым индексом гораздо более гибкий (так как он поддерживает запросы по любому ключу), индексы.
конкретных выражений скорее всего будут меньше и быстрее, чем простые индексы.

https://postgrespro.ru/docs/postgresql/10/datatype-json
...
Рейтинг: 0 / 0
07.05.2018, 23:42
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Условие WHERE внутри JSON поля postgresql?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39641654&tid=1995784]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 15ms
total:  133ms
созд. / загр.: 133ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]