I have the following json string in mytbl.json_data column:
{"resourceType":"QuestionnaireResponse","extension":[{"url":"ppp","valueCode":"en"}],"identifier":{"value":"222222"},"status":"completed","subject":{"reference":"Patient/12345"},"authored":"2024-01-17T20:13:46+00:00","group":{"linkId":"root_group","title":"Demographics","question":[{"linkId":"104573","text":"What is the highest grade of school you have completed","answer":[{"valueInteger":2}]},{"linkId":"333","text":"What describes your current marital status? ","answer":[{"valueInteger":1}]}]}}
When I query the "group" field I get null:
SELECT s.json_data.group FROM mytbl s;
When I rename "group" field to"group_1" I get the correct value.
I need to be able to use "group" field as this is what we get from the vendor. How can I do it?
I am using SQL Developer.
Thank you!!!
SQL> create table t ( j json );
Table created.
SQL> begin
2 insert into t values (
3 '{"resourceType":"QuestionnaireResponse",
4 "extension":[{"url":"ppp","valueCode":"en"}],
5 "identifier":{"value":"222222"},
6 "status":"completed",
7 "subject":{"reference":"Patient/12345"},
8 "authored":"2024-01-17T20:13:46+00:00",
9 "group":{"linkId":"root_group","title":"Demographics"}
10 }');
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT s.j.group FROM t s;
SELECT s.j.group FROM t s
*
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
SQL>
SQL> SELECT s.j."group" FROM t s;
group
---------------------------------------------------------------------------
{"linkId":"root_group","title":"Demographics"}