here is json column in Oracle DB table.
table name : test1
| ITEM_ID | CAT1 | CAT2 | POS_VAL_JSON |
|---|---|---|---|
| ITEM1 | A | 1 | {"X" : [1,2,3], "Y" : [1,2,3]} |
table name : test2
| ITEM_ID | CAT1 | CAT2 | X | Y |
|---|---|---|---|---|
| ITEM1 | A | 1 | {1,2,3} | {1,2,3} |
How can I get data like this from table1 and table2 each ?
| X | Y | ITEM_ID |
|---|---|---|
| 1 | 1 | ITEM1 |
| 2 | 2 | ITEM1 |
| 3 | 3 | ITEM1 |
CodePudding user response：
You can use JSON_TABLE to get the X values with FOR ORDINALITY to get an index and then the same for the Y values and join:
SELECT x, y, item_id
FROM test1 t
CROSS JOIN LATERAL (
SELECT idx, x
FROM JSON_TABLE(
t.pos_val_json,
'$.X[*]'
COLUMNS (
idx FOR ORDINALITY,
x NUMBER PATH '$'
)
)
) x
INNER JOIN LATERAL (
SELECT idx, y
FROM JSON_TABLE(
t.pos_val_json,
'$.Y[*]'
COLUMNS (
idx FOR ORDINALITY,
y NUMBER PATH '$'
)
)
) y
ON (x.idx = y.idx)
Which, for the sample data:
CREATE TABLE test1 (
ITEM_ID VARCHAR2(20),
CAT1 VARCHAR2(20),
CAT2 NUMBER,
POS_VAL_JSON CLOB CHECK (POS_VAL_JSON IS JSON)
);
INSERT INTO test1 (ITEM_ID, CAT1, CAT2, POS_VAL_JSON)
VALUES ('ITEM1', 'A', 1, '{"X" : [1,2,3], "Y" : [1,2,3]}');
Outputs:
X Y ITEM_ID 1 1 ITEM1 2 2 ITEM1 3 3 ITEM1
Similarly, for test2 you can use:
SELECT x.x, y.y, t.item_id
FROM test2 t
CROSS JOIN LATERAL (
SELECT idx, x
FROM JSON_TABLE(
t.X,
'$[*]'
COLUMNS (
idx FOR ORDINALITY,
x NUMBER PATH '$'
)
)
) x
INNER JOIN LATERAL (
SELECT idx, y
FROM JSON_TABLE(
t.Y,
'$[*]'
COLUMNS (
idx FOR ORDINALITY,
y NUMBER PATH '$'
)
)
) y
ON (x.idx = y.idx)
db<>fiddle here
CodePudding user response：
this code works for test2:
WITH
test2 (ITEM_ID,
cat1,
cat2,
X,
Y)
AS
(SELECT 'item1',
'A',
1,
json_array (1, 2, 3),
json_array (1, 2, 3)
FROM DUAL),
step2
AS
(SELECT test2.item_id, t1.name X, ROWNUM num
FROM test2
CROSS APPLY
JSON_TABLE (
test2.X,
'$'
COLUMNS (NESTED PATH '$[*]' COLUMNS (name PATH '$'))) t1),
step3
AS
(SELECT test2.item_id, t1.name Y, ROWNUM num
FROM test2
CROSS APPLY
JSON_TABLE (
test2.X,
'$'
COLUMNS (NESTED PATH '$[*]' COLUMNS (name PATH '$'))) t1)
SELECT step3.item_id, step3.Y, step2.X
FROM step3 JOIN step2 ON step2.num = step3.num
this code works for test1
WITH
test1 (ITEM_ID,
cat1,
cat2,
pos_val_json)
AS
(SELECT 'item1',
'A',
1,
json_object ('X' VALUE json_array (1, 2, 3),
'Y' VALUE json_array (1, 2, 3))
FROM DUAL),
step2
AS
(SELECT test1.item_id, t1.*,rownum num
FROM test1
CROSS APPLY
JSON_TABLE (
test1.pos_val_json,
'$'
COLUMNS (NESTED PATH '$.X[*]' COLUMNS (X PATH '$'))) t1
) ,step3 as (SELECT test1.item_id, t2.*,rownum num
FROM test1
CROSS APPLY
JSON_TABLE (
test1.pos_val_json,
'$'
COLUMNS (NESTED PATH '$.Y[*]' COLUMNS (Y PATH '$'))) t2)
SELECT step3.item_id,step3.Y,step2.X
FROM step3 join step2 on step2.num=step3.num
X Y ITEM_ID 1 1 ITEM1 2 2 ITEM1 3 3 ITEM1
this code works for test1 only if the array has 3 values
WITH
test1 (ITEM_ID,
cat1,
cat2,
pos_val_json)
AS
(SELECT 'item1',
'A',
1,
json_object ('X' VALUE json_array (1, 2, 3),
'Y' VALUE json_array (1, 2, 3))
FROM DUAL),
test2 (ITEM_ID,
cat1,
cat2,
X,
Y)
AS
(SELECT 'item1',
'A',
1,
json_array (1, 2, 3),
json_array (1, 2, 3)
FROM DUAL),
step2
AS
(SELECT test1.item_id,
t.*
FROM test1
CROSS APPLY
JSON_TABLE (
test1.pos_val_json,
'$'
COLUMNS ("X0" PATH '$.X[0]',
"X1" PATH '$.X[1]',
"X2" PATH '$.X[2]',
"Y0" PATH '$.Y[0]',
"Y1" PATH '$.Y[1]',
"Y2" PATH '$.Y[2]'
)) t),
step3 as( SELECT *
FROM step2
unpivot(
(x_value,y_value)
for xy_name
in ((x0,y0) as 'xy_0',
(x1,y1) as 'xy_1',
(x2,y2) as 'xy_2')
)
)
select item_id,X_value as x, y_value as y from step3
this code works for test2 only if the array has 3 values
WITH
test2 (ITEM_ID,
cat1,
cat2,
X,
Y)
AS
(SELECT 'item1',
'A',
1,
json_array (1, 2, 3),
json_array (1, 2, 3)
FROM DUAL),
step2
AS
(
SELECT test2.item_id,
t1.*,t2.*
FROM test2
CROSS APPLY
JSON_TABLE (
test2.X,
'$'
COLUMNS ("X0" PATH '$[0]',
"X1" PATH '$[1]',
"X2" PATH '$[2]'
)) t1
CROSS APPLY
JSON_TABLE (
test2.Y,
'$'
COLUMNS ("Y0" PATH '$[0]',
"Y1" PATH '$[1]',
"Y2" PATH '$[2]'
)) t2) , --select * from step2
step3 as( SELECT *
FROM step2
unpivot(
(x_value,y_value)
for xy_name
in ((x0,y0) as 'xy_0',
(x1,y1) as 'xy_1',
(x2,y2) as 'xy_2')
)
)
select item_id,X_value as x, y_value as y from step3