|
30.12.2021, 10:40
#40124114
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Нижний Новгород
Сообщения: 1 928
Рейтинг:
0
/ 0
|
|
|
|
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.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
Connected to Oracle Database 18c Express Edition Release 18.0.0.0.0
Connected as user1@DBOFFICEXE
SQL>
SQL> with t(text) as (
2 select '{
3 "object": {
4 "inf_doc_selling": [
5 {
6 "ID_HEAD": "2b453b25-5282-11ec-8c50-a8a159650ac5",
7 "ID_TYPEDOC": "Реализация товаров и услуг",
8 "ID_CLIENT": "d2b6bc1e-9507-11e1-9f00-00155d001304",
9 "ID_CLIENT2": "d2b6bc1e-9507-11e1-9f00-00155d001304",
10 "ID_DEP": "8255cef2-f9ec-11eb-8c45-a8a159650ac5",
11 "NUM_HEAD": "0Н00-000012",
12 "DATE_HEAD": "01.11.2021 0:00:00",
13 "CLOSED": 0,
14 "PARENT_ID_HEAD": "6d501ad1-5280-11ec-8c50-a8a159650ac5",
15 "GOODS": [
16 {
17 "ID_WARE": "acc3f047-fab3-11e7-b296-3cd92beecda8",
18 "LINENO": 1,
19 "QUANTITY": 1,
20 "PRICE1": 1665,
21 "NDS": 20
22 },
23 {
24 "ID_WARE": "ad814e98-543b-11e8-b048-3cd92beecda8",
25 "LINENO": 2,
26 "QUANTITY": 1,
27 "PRICE1": 2475,
28 "NDS": 20
29 },
30 {
31 "ID_WARE": "8e58299e-0bd8-11e8-848e-3cd92beecda8",
32 "LINENO": 3,
33 "QUANTITY": 1,
34 "PRICE1": 1655,
35 "NDS": 20
36 },
37 {
38 "ID_WARE": "6d9b0890-fab5-11e7-b296-3cd92beecda8",
39 "LINENO": 4,
40 "QUANTITY": 1,
41 "PRICE1": 1910,
42 "NDS": 20
43 }
44 ]
45 },
46 {
47 "ID_HEAD": "a993d1ec-5289-11ec-8c50-a8a159650ac5",
48 "ID_TYPEDOC": "Реализация товаров и услуг",
49 "ID_CLIENT": "c6a7e7fd-9507-11e1-9f00-00155d001304",
50 "ID_CLIENT2": "c6a7e7fd-9507-11e1-9f00-00155d001304",
51 "ID_DEP": "8255cef2-f9ec-11eb-8c45-a8a159650ac5",
52 "NUM_HEAD": "0Н00-000018",
53 "DATE_HEAD": "01.11.2021 0:00:00",
54 "CLOSED": 0,
55 "PARENT_ID_HEAD": "7c99f509-5289-11ec-8c50-a8a159650ac5",
56 "GOODS": [
57 {
58 "ID_WARE": "5f165d9b-8916-11e5-942d-3cd92beecda8",
59 "LINENO": 1,
60 "QUANTITY": 1,
61 "PRICE1": 1675,
62 "NDS": 20
63 },
64 {
65 "ID_WARE": "a63b449f-4ac0-11e4-a1bb-3cd92beecda8",
66 "LINENO": 2,
67 "QUANTITY": 1,
68 "PRICE1": 2195,
69 "NDS": 20
70 },
71 {
72 "ID_WARE": "06b93f24-a7eb-11e2-9aa6-00237dd69f8b",
73 "LINENO": 3,
74 "QUANTITY": 1,
75 "PRICE1": 1175,
76 "NDS": 20
77 }
78 ]
79 },
80 {
81 "ID_HEAD": "f3ec6746-68af-11ec-8739-ac1f6b3e3ec3",
82 "ID_TYPEDOC": "Реализация товаров и услуг",
83 "ID_CLIENT": "1911ca68-2b4b-11e3-9f94-00237dd69f8b",
84 "ID_CLIENT2": "1911ca68-2b4b-11e3-9f94-00237dd69f8b",
85 "ID_DEP": "00000000-0000-0000-0000-000000000000",
86 "NUM_HEAD": "0Н00-000241",
87 "DATE_HEAD": "29.12.2021 17:01:42",
88 "CLOSED": 0,
89 "PARENT_ID_HEAD": "",
90 "GOODS": [
91 {
92 "ID_WARE": "19a97c92-dc5e-11e1-ba3a-00155d001304",
93 "LINENO": 1,
94 "QUANTITY": 1,
95 "PRICE1": 500,
96 "NDS": 0
97 }
98 ]
99 }
100 ]
101 }
102 }' from dual
103 )
104 select j.id_head, j.num_head, j.id_ware
105 from t, json_table(t.text, '$.object.inf_doc_selling[*]'
106 columns( ID_HEAD path '$.ID_HEAD'
107 ,NUM_HEAD path '$.NUM_HEAD'
108 ,ID_WARE path '$.GOODS.ID_WARE'
109 )
110 ) j;
ID_HEAD NUM_HEAD ID_WARE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2b453b25-5282-11ec-8c50-a8a159650ac5 0Н00-000012
a993d1ec-5289-11ec-8c50-a8a159650ac5 0Н00-000018
f3ec6746-68af-11ec-8739-ac1f6b3e3ec3 0Н00-000241 19a97c92-dc5e-11e1-ba3a-00155d001304
SQL>
1.
2.
3.
4.
5.
select j.id_ware--, j.num_head, j.id_ware
from t, json_table(t.text, '$.object.inf_doc_selling[*].goods[*]'
columns( ID_WARE path '$.ID_WARE'
)
) j;
Так - пусто.
Как правильно? |
|
|
