By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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]}');
1 rows affected
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)
| X | Y | ITEM_ID |
|---|---|---|
| 1 | 1 | ITEM1 |
| 2 | 2 | ITEM1 |
| 3 | 3 | ITEM1 |
SELECT x, y, item_id
FROM test1 t
CROSS APPLY JSON_TABLE(
t.pos_val_json,
'$.X[*]'
COLUMNS (
idx FOR ORDINALITY,
x NUMBER PATH '$'
)
) x
CROSS APPLY JSON_TABLE(
t.pos_val_json,
'$.Y[*]'
COLUMNS (
idx FOR ORDINALITY,
y NUMBER PATH '$'
)
) y
WHERE x.idx = y.idx
| X | Y | ITEM_ID |
|---|---|---|
| 1 | 1 | ITEM1 |
| 2 | 2 | ITEM1 |
| 3 | 3 | ITEM1 |
CREATE TABLE test2 (
ITEM_ID VARCHAR2(20),
CAT1 VARCHAR2(20),
CAT2 NUMBER,
X CLOB CHECK (X IS JSON),
Y CLOB CHECK (Y IS JSON)
)
INSERT INTO test2 (ITEM_ID, CAT1, CAT2, X, Y)
VALUES ('ITEM1', 'A', 1, '[1,2,3]', '[1,2,3]');
1 rows affected
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)
| X | Y | ITEM_ID |
|---|---|---|
| 1 | 1 | ITEM1 |
| 2 | 2 | ITEM1 |
| 3 | 3 | ITEM1 |
SELECT x.x, y.y, t.item_id
FROM test2 t
CROSS APPLY JSON_TABLE(
t.X,
'$[*]'
COLUMNS (
idx FOR ORDINALITY,
x NUMBER PATH '$'
)
) x
CROSS APPLY JSON_TABLE(
t.Y,
'$[*]'
COLUMNS (
idx FOR ORDINALITY,
y NUMBER PATH '$'
)
) y
WHERE x.idx = y.idx
| X | Y | ITEM_ID |
|---|---|---|
| 1 | 1 | ITEM1 |
| 2 | 2 | ITEM1 |
| 3 | 3 | ITEM1 |