Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE test_json1 (id int, json_date clob CHECK (json_date IS JSON) );
insert into test_json1 (id,json_date)
SELECT 2, '{"fl":{"12/07/2023":2000,"12/08/2023":3000,"12/31/2023":4000,"12/13/2023":5000},"TIME1":{"12/07/2023":0,"12/31/2023":6000}}'  FROM DUAL UNION ALL
SELECT 1, '{"fl":{"12/07/2023":1000,"12/07/2023":1000,"12/31/2023":1500,"12/13/2023":2000},"TIME1":{"12/07/2023":0,"12/31/2023":5000}}'  FROM DUAL UNION ALL
SELECT 1,'{"fl":{"12/07/2023":150,"12/31/2023":250,"12/13/2023":350},"TIME1":{"12/07/2023":0,"12/31/2023":100}}'  FROM DUAL;
3 rows affected

SELECT t.id,
       dg.structure
FROM   test_json1 t
       CROSS APPLY (
         SELECT JSON_DATAGUIDE(t.json_date) AS structure
         FROM   DUAL
       ) dg;


ID
STRUCTURE


2
[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":128},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/08/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]

1
[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":128},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]

1
[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":64},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]


SELECT t.id,
       j.pos,
       j.value
FROM   test_json1 t
       CROSS APPLY JSON_TABLE(
         t.json_date,
         '$.fl.*'
         COLUMNS (
           pos   FOR ORDINALITY,
           value PATH '$'
         )
       ) j;


ID
POS
VALUE


2
1
2000

2
2
3000

2
3
4000

2
4
5000

1
1
1000

1
2
1000

1
3
1500

1
4
2000

1
1
150

1
2
250

1
3
350


CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/
CREATE FUNCTION num_keys(
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys.COUNT;
END num_keys;
/
SELECT t.id,
       j.pos,
       CASE
       WHEN pos <= num_keys(t.json_date.fl)
       THEN get_key(j.pos, t.json_date.fl)
       END AS key,
       j.value
FROM   test_json1 t
       CROSS APPLY JSON_TABLE(
         t.json_date,
         '$.fl.*'
         COLUMNS (
           pos   FOR ORDINALITY,
           value PATH '$'
         )
       ) j;


ID
POS
KEY
VALUE


2
1
12/07/2023
2000

2
2
12/08/2023
3000

2
3
12/31/2023
4000

2
4
12/13/2023
5000

1
1
12/07/2023
1000

1
2
12/31/2023
1000

1
3
12/13/2023
1500

1
4
null
2000

1
1
12/07/2023
150

1
2
12/31/2023
250

1
3
12/13/2023
350


SELECT id,
       REGEXP_SUBSTR(
         json_date,
         '"(([^"]|\\")*)":\s*(\d+)[^}]*,\s*"\1":\s*\3',
         1,
         1,
         NULL,
         1
       ) AS key,
       REGEXP_SUBSTR(
         json_date,
         '"(([^"]|\\")*)":\s*(\d+)[^}]*,\s*"\1":\s*\3',
         1,
         1,
         NULL,
         3
       ) AS value
FROM   test_json1
WHERE  REGEXP_LIKE(json_date, '"(([^"]|\\")*)":\s*(\d+)[^}]*,\s*"\1":\s*\3')


ID
KEY
VALUE


1
12/07/2023
1000

ID	STRUCTURE
2	[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":128},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/08/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]
1	[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":128},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]
1	[{"o:path":"$","type":"object","o:length":128},{"o:path":"$.fl","type":"object","o:length":64},{"o:path":"$.fl."12/07/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/13/2023"","type":"number","o:length":4},{"o:path":"$.fl."12/31/2023"","type":"number","o:length":4},{"o:path":"$.TIME1","type":"object","o:length":64},{"o:path":"$.TIME1."12/07/2023"","type":"number","o:length":1},{"o:path":"$.TIME1."12/31/2023"","type":"number","o:length":4}]

ID	POS	VALUE
2	1	2000
2	2	3000
2	3	4000
2	4	5000
1	1	1000
1	2	1000
1	3	1500
1	4	2000
1	1	150
1	2	250
1	3	350

ID	POS	KEY	VALUE
2	1	12/07/2023	2000
2	2	12/08/2023	3000
2	3	12/31/2023	4000
2	4	12/13/2023	5000
1	1	12/07/2023	1000
1	2	12/31/2023	1000
1	3	12/13/2023	1500
1	4	null	2000
1	1	12/07/2023	150
1	2	12/31/2023	250
1	3	12/13/2023	350