I want to find the number of rows in each json object. For example,
SELECT A.firstName
FROM ABC c,
JSON_TABLE (c.abc_data, '$'
COLUMNS (NESTED PATH '$.names[*]' COLUMNS (firstName PATH '$.value'))) AS A;
In the above code there are multiple objects like $.names[*] and each of these object has multiple sub elements like firstName PATH '$.value'. I want to find out the count of rows that is present in each object like $.names[*].
Assuming you have the table and data:
CREATE TABLE abc ( abc_data CLOB CHECK ( abc_data IS JSON ) );
INSERT INTO abc (
abc_data
) VALUES (
'{"names": [
{"value":1},
{"value":2},
{"value":3},
{"value":4}
]
}'
);
Then, as per this answer, you can use:
SELECT a.firstname,
JSON_VALUE(a.sz, '$[0]') AS sz
FROM ABC c
CROSS APPLY JSON_TABLE(
c.abc_data,
'$'
COLUMNS (
NESTED PATH '$.names[*]' COLUMNS (
firstName PATH '$.value'
),
sz VARCHAR2(10) FORMAT JSON WITH WRAPPER PATH '$.names.size()'
)
) AS A;
or:
SELECT a.firstname,
COUNT(*) OVER (PARTITION BY c.ROWID) AS sz
FROM ABC c
CROSS APPLY JSON_TABLE(
c.abc_data,
'$'
COLUMNS (
NESTED PATH '$.names[*]' COLUMNS (
firstName PATH '$.value'
)
)
) AS A;
Which both output:
FIRSTNAME SZ 1 4 2 4 3 4 4 4
db<>fiddle here
