I have a table structure as below:
create table json_tab (id number constraint primary key,d_data clob(30000));
insert into json_tab values (1,'[{"a"=0,"b"=1},{"a"=1,"b"=2},{"a"=2,"b"=3}]');
insert into json_tab values (2,'[{"a"=0,"b"=1},{"a"=1,"b"=2},{"a"=2,"b"=3}]');
I want to write a query to fetch records as below format.
| id | a | b |
|---|---|---|
| 1 | 0 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 2 | 0 | 1 |
| 2 | 1 | 2 |
| 2 | 2 | 3 |
Above is an example data. Real data is for JSON column more than 30000 bytes
To begin with, your SQL code is not correct, neither for the create table, nor for the json document. Here's a corrected version:
drop table json_tab;
create table json_tab (id number primary key,d_data clob check (d_data is json));
insert into json_tab values (1,'[{"a":0,"b":1},{"a":1,"b":2},{"a":2,"b":3}]');
insert into json_tab values (2,'[{"a":0,"b":1},{"a":1,"b":2},{"a":2,"b":3}]');
select j.id, t.a, t.b
from json_tab j, json_table (j.d_data columns (
nested path '$[*]' COLUMNS(
A NUMBER PATH '$.a',
B number path '$.b'))) t;
And here's the output:
ID A B
---------- ---------- ----------
1 0 1
1 1 2
1 2 3
2 0 1
2 1 2
2 2 3
You can create your table as:
create table json_tab (
id NUMBER(8,0) CONSTRAINT json_tab__id__pk PRIMARY KEY,
d_data CLOB CONSTRAINT json_tab__d_data__json CHECK (d_data IS JSON)
);
Then your JSON data should use "a":1 rather than "a"=1 in the object's key-value pairs:
insert into json_tab ( id, d_data )
SELECT 1,'[{"a":0,"b":1},{"a":1,"b":2},{"a":2,"b":3}]' FROM DUAL UNION ALL
SELECT 2,'[{"a":0,"b":1},{"a":1,"b":2},{"a":2,"b":3}]' FROM DUAL;
Then you can use a JSON_TABLE:
SELECT id,a,b
FROM json_tab t
CROSS JOIN
JSON_TABLE(
t.d_data,
'$[*]'
COLUMNS
a NUMBER(5,0) PATH '$.a',
b NUMBER(5,0) PATH '$.b'
)
Which outputs:
ID | A | B -: | -: | -: 1 | 0 | 1 1 | 1 | 2 1 | 2 | 3 2 | 0 | 1 2 | 1 | 2 2 | 2 | 3
db<>fiddle here
