Querying Structs in Oracle

I am querying a dataset in Oracle that has some columns formatted as structs.

Take the following table:

| id | person                                              |
| -- | --------------------------------------------------- |
| 1  | {"firstName":"John","lastName":"Smith","age":"10"}  |
| 2  | {"firstName":"Jane","lastName":"Smith","age":"12"}  |
| 3  | {"firstName":"Bob","lastName":"White","age":"13"}   |
| 4  | {"firstName":"Betty","lastName":"White","age":"11"} |

This is the desired result:

| id | firstname | lastname | age |
| -- | --------- | -------- | --- |
| 1  | John      | Smith    | 10  |
| 2  | Jane      | Smith    | 12  |
| 3  | Bob       | White    | 13  |
| 4  | Betty     | White    | 11  |

I have been able to get this desired result in other SQL editors, but the same syntax in Oracle gives me the error ORA-00904: "PERSON"."FIRSTNAME": invalid identifier

Other Method:

SELECT
    id,
    person.firstName,
    person.lastName,
    person.age
FROM
    table

I feel like there is an easy way to query this, but my search on Stack Overflow and Google has come up empty so far.

Solution

Assuming that:

your data is valid JSON (yours isn't but it is close enough that you may have mistyped it and you say that your query works with other editors which makes it seem like it should be a known format); and
you are connecting to an Oracle database (SQL Developer is typically used with Oracle database but can connect to others)

Then use the table name/alias before the column name:

SELECT id,
       t.person.firstName,
       t.person.lastName,
       t.person.age
FROM   table_name t;

or use JSON_TABLE to parse the data:

SELECT id,
       j.*
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.person,
         '$'
         COLUMNS (
           firstname VARCHAR2(20) PATH '$.firstName',
           lastname  VARCHAR2(20) PATH '$.lastName',
           age       NUMBER       PATH '$.age'
         )
       ) j;

Which, for the sample data with valid JSON (note the quotes around the firstName value):

CREATE TABLE table_name (
  id NUMBER PRIMARY KEY,
  person CLOB CHECK (person IS JSON)
);

INSERT INTO table_name (id, person)
  SELECT 1, '{"firstName":"John","lastName":"Smith","age":"10"}' FROM DUAL UNION ALL
  SELECT 2, '{"firstName":"Jane","lastName":"Smith","age":"12"}' FROM DUAL UNION ALL
  SELECT 3, '{"firstName":"Bob","lastName":"White","age":"13"}' FROM DUAL UNION ALL
  SELECT 4, '{"firstName":"Betty","lastName":"White","age":"11"}' FROM DUAL;

Both output:

ID	FIRSTNAME	LASTNAME	AGE
1	John	Smith	10
2	Jane	Smith	12
3	Bob	White	13
4	Betty	White	11

fiddle