Skip to Main Content
  • Questions
  • dealing with the word "group in selecting json values

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 29, 2024 - 8:46 pm UTC

Last updated: February 05, 2024 - 1:28 am UTC

Version: Oracle 19c

Viewed 100+ times

You Asked

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!!!


and Connor said...

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"}

Rating

  (2 ratings)

Comments

A reader, February 06, 2024 - 4:34 am UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library