Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Использование json_exists / 10 сообщений из 10, страница 1 из 1
24.09.2019, 15:30
    #39866422
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
Всем привет

Ранее не особо не работала с json, но вот нужда заставила :) Не совсем понимаю, можно ли параметром передавать построчно значение колонки в json_exists.
Пример запроса:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH t AS
(
   SELECT 101 id FROM dual
   UNION ALL
   SELECT 102 id from dual
),
n AS
(
   SELECT json_array('101') id_list FROM dual
   UNION ALL
   SELECT json_array('102') id_list FROM dual
)
SELECT * 
  FROM n,
       t
  WHERE json_exists(n.id_list,'$?(@ == $i)' PASSING t.id||'' as "i");


Результат выполнения:
id_list id["101"] 101["101"] 102
Необходимый/ожидаемый результат:
id_list id["101"] 101["102"] 102
По идее можно распарсить через json_table сначала и использовать, но вдруг я чего-то не понимаю, ткните, плиз, в РТФМ.

PS. Не использовать json не предлагать, внутренний стандарт требует именно его в данном случае.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 16:08
    #39866451
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
karbka,

В общем, да, походу json_exists только для фильтрации.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
WITH t AS
(
   SELECT 102 id from dual
   UNION ALL
   SELECT 101 id FROM dual
),
n AS
(
   SELECT json_array('101','1569') id_list FROM dual
   UNION ALL
   SELECT json_array('102') id_list FROM dual
)
SELECT t.id,
       n.id_list 
  FROM t,
       n, 
       (
          json_table
          (
             n.id_list, '$[*]'
             COLUMNS 
             (
                id_json VARCHAR2 PATH '$[*]'
             )
                         
          )
       )
 WHERE t.id = id_json;


id id_list101 ["101";"1569"]102 ["102"]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 16:52
    #39866487
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
karbka,

c jonson не работал

поетому странно что результат зависит от порядка строк

Код: plsql
1.
2.
3.
4.
5.
6.
WITH t AS
(
   SELECT '103' id FROM dual
   UNION ALL
   SELECT '101' id from dual
),


и

Код: plsql
1.
2.
3.
4.
5.
6.
WITH t AS
(
   SELECT '101' id FROM dual
   UNION ALL
   SELECT '103' id from dual
),




.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 16:54
    #39866492
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
C json-ом тоже никогда не работал, кинулся читать доку по 18 и 19 .
https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/condition-JSON_EXISTS.html#GUID-D60A7E52-8819-4D33-AEDB-223AB7BDE60A The second argument to json_exists is a SQL/JSON path expression followed by an optional PASSING clause and an optional error clause.

The optional filter expression of a SQL/JSON path expression used with json_exists can refer to SQL/JSON variables, whose values are passed from SQL by binding them with the PASSING clause. The following SQL data types are supported for such variables: VARCHAR2, NUMBER, BINARY_DOUBLE, DATE, TIMESTAMP, and TIMESTAMP WITH TIMEZONE.
С clause passing (по крайней мере на этих страницах документации) подло не опубликовали ни одного примера.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
column id_list format a10

WITH t AS
(
   SELECT 101 id FROM dual
   UNION ALL
   SELECT 102 id from dual
),
n AS
(
   SELECT json_array('100') id_list FROM dual
   UNION ALL
   SELECT json_array('101') id_list FROM dual
   UNION ALL
   SELECT json_array('102') id_list FROM dual
   UNION ALL
   SELECT json_array('103') id_list FROM dual
   UNION ALL
   SELECT json_array('104') id_list FROM dual
)
SELECT n.*, t.*, 
case when json_exists(n.id_list,'$?(@ == $i)' PASSING to_char(t.id + 0) as "i") then 'exists' end ex0,
case when json_exists(n.id_list,'$?(@ == $i)' PASSING to_char(t.id + 1) as "i") then 'exists' end ex1,
case when json_exists(n.id_list,'$?(@ == $i)' PASSING to_char(t.id + 2) as "i") then 'exists' end ex2
  FROM n,
       t
where id_list is json;

ID_LIST            ID EX0    EX1    EX2
---------- ---------- ------ ------ ------
["100"]           101               
["101"]           101 exists        
["102"]           101        exists 
["103"]           101               exists
["104"]           101               
["100"]           102               
["101"]           102 exists        
["102"]           102        exists 
["103"]           102               exists
["104"]           102               

10 rows selected


Такое ощущение, что passing хватает значение при доступе к первой обработанной записи и кеширует его у себя в кишках, игнорируя значения во всех остальных строках, выдавая тем самым неверный результат.
При попытке же склеить значение в конкатенации ругается ORA-00907:
Код: plsql
1.
case when json_exists(n.id_list,'$?(@ == ' || to_char(t.id) || ')') then 'exists' end ex00
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 17:09
    #39866508
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
Stax, AmKad,

Да, заметила это тоже, спасибо...

Короче заюзала json_table и не буду париться. Вообще это задумывалось как универсальная структура для облегчения работы с множествами. В итоге для инициализацинного скрипта таблицы написала кучу кастомного кода (три таких стоблца), чтобы не было перечений множеств при вставке (констрейнты не повесишь) и теперь при непосредственном запросе куча танцев с бубном. Ну, такая себе оптимизация. Есть подозрение, что комбинаций для правил будет немного, так что я бы вообще создала плоскую таблицу с необходимыми комбинациями, повесила констрейнт на уникальность и жила бы себе счастливо :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 17:35
    #39866532
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
Ну и по складывающейся традиции AmKad-овский костыль, работающий далеко не на всех версиях - делаем PL/SQL функцию. 18-ой у меня под рукой нет.
 
12.2.0.1.0 - не работает
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
column id_list format a10

select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

Elapsed: 00:00:00.00

with function f1(p_str varchar2, p_num number) return number as
v_num number := 0;
begin
    select count(*)
    into v_num
    from dual
    where json_exists(p_str,'$?(@ == $i)' PASSING to_char(p_num) as "i");

    return v_num;
end;
t AS
(
   SELECT 101 id FROM dual
   UNION ALL
   SELECT 102 id from dual
),
n AS
(
   SELECT json_array('100') id_list FROM dual
   UNION ALL
   SELECT json_array('101') id_list FROM dual
   UNION ALL
   SELECT json_array('102') id_list FROM dual
   UNION ALL
   SELECT json_array('103') id_list FROM dual
   UNION ALL
   SELECT json_array('104') id_list FROM dual
)
select t.*, n.*
from t, n
where f1(id_list, id) = 1
/

        ID ID_LIST
---------- ----------
       101 ["101"]
       102 ["101"]

Elapsed: 00:00:00.01

 
19.4.0.0.0 - лишь бы не сглазить
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
column id_list format a10

select version_full from product_component_version;

VERSION_FULL
-----------------------------------------------------------------------------------
19.4.0.0.0

Elapsed: 00:00:00.01

with function f1(p_str varchar2, p_num number) return number as
v_num number := 0;
begin
    select count(*)
    into v_num
    from dual
    where json_exists(p_str,'$?(@ == $i)' PASSING to_char(p_num) as "i");

    return v_num;
end;
t AS
(
   SELECT 101 id FROM dual
   UNION ALL
   SELECT 102 id from dual
),
n AS
(
   SELECT json_array('100') id_list FROM dual
   UNION ALL
   SELECT json_array('101') id_list FROM dual
   UNION ALL
   SELECT json_array('102') id_list FROM dual
   UNION ALL
   SELECT json_array('103') id_list FROM dual
   UNION ALL
   SELECT json_array('104') id_list FROM dual
)
select t.*, n.*
from t, n
where f1(id_list, id) = 1
/

        ID ID_LIST
---------- ----------
       101 ["101"]
       102 ["102"]

Elapsed: 00:00:00.01

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2019, 17:54
    #39866546
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
AmKad,

Ахах, это я в связи с тем, что слишком часто пишу на форум забыла указать версию, сорян :))

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.09.2019, 13:46
    #39866862
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
Всем спасибо, остановилась на варианте
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
WITH t AS
(
   SELECT 102 id from dual
   UNION ALL
   SELECT 101 id FROM dual
),
n AS
(
   SELECT json_array(89,101,1569) id_list FROM dual
   UNION ALL
   SELECT json_array(102) id_list FROM dual
)
SELECT t.id,
       n.id_list 
  FROM t,
       n, 
       (
          json_table
          (
             n.id_list, '$[*]'
             COLUMNS 
             (
                id_json VARCHAR2 PATH '$[*]'
             )
          )
       )
 WHERE t.id = id_json;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.09.2019, 14:27
    #39866894
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
karbka,

В случае, если в массиве будут неуникальные элементы, этот запрос может дать "размножение" записей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.09.2019, 22:18
    #39867110
karbka
karbka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Использование json_exists
AmKad,

Да, уникальность контролирую на этапе вставки в справочник, должно быть все норм :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Использование json_exists / 10 сообщений из 10, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 396ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1882042]:
 0ms
get settings:
 12ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 264ms
total:  396ms
x
x
Закрыть


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