Oracle 18c

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

CREATE TABLE test (id INT, val VARCHAR(2000));
INSERT INTO test VALUES (1, '[{"choice":[{"type":"ab","id":3453,"b_id":8989,"shar":""}],"condition":"qwert"},{"choice":[{"type":"ab","id":5436,"b_id":7865,"shar":""}],"condition":"uyutor"}]');
1 rows affected

SELECT * FROM test;


ID
VAL


1
[{"choice":[{"type":"ab","id":3453,"b_id":8989,"shar":""}],"condition":"qwert"},{"choice":[{"type":"ab","id":5436,"b_id":7865,"shar":""}],"condition":"uyutor"}]


SELECT test.id json_id, parsed_json.*
FROM test,
JSON_TABLE(val, '$[*].choice'
COLUMNS
  (row_number FOR ORDINALITY,
   type VARCHAR2(100) PATH '$.type',
   id INT PATH '$.id',
   b_id INT PATH '$.b_id',
   shar VARCHAR2(100) PATH '$.shar'
   )) AS parsed_json;


JSON_ID
ROW_NUMBER
TYPE
ID
B_ID
SHAR


1
1
ab
3453
8989
null

1
2
ab
5436
7865
null