How I can define the @ symbol path on json_table oracle query-CodePudding

I have the json data like this below:

[{"@code":null,"@name":"Model 3","@id":"{Model 3}"},{"@code":5010020168,"@name":"Tesla Inc.","@id":"[COM:/acorn=5010020168]"}]

How I can make ORACLE query define the path for '@' symbol.

I tried like this below and doesn't work:

select 
    mv.id,
    mv.value as title, 
    qjson_options.row_number,
    qjson_options.code,
    qjson_options.name,
    qjson_options.id
from meta_value mv, 
    JSON_TABLE(mv.additional_info, '$.[*]'
    COLUMNS (
        row_number FOR ORDINALITY,
        code VARCHAR2(255) PATH '$.@code',
        id VARCHAR2(255) PATH '$.@id',
        name VARCHAR2(255) PATH '$.@name'
    )) qjson_options

THANKS

CodePudding user response：

There are two issues in your attempted code.

One is a fatal error that wouldn't even let you get close to the @... names. Specifically, the context path $.[*] is invalid. What you need there is $[*]. Your top-level structure is an array, and the path must reflect that. There should be no period between $ and the array marker [*].

The second is - and this answers your question - that the names with a leading at-sign must be enclosed in double-quotes.

Something like this (tested on Oracle 12.2.0.1):

select *
from   json_table(
         '[{"@code":null,"@name":"Model 3","@id":"{Model 3}"},
           {"@code":5010020168,"@name":"Tesla Inc.",
            "@id":"[COM:/acorn=5010020168]"}]'
         , '$[*]'                -- <<< First mistake was here
         COLUMNS (
           row_number FOR ORDINALITY,
           code VARCHAR2(255) PATH '$."@code"',
           id   VARCHAR2(255) PATH '$."@id"',
           name VARCHAR2(255) PATH '$."@name"'
         )
       )
;

    
ROW_NUMBER CODE           ID                       NAME          
---------- -------------- ------------------------ --------------
         1                {Model 3}                Model 3       
         2 5010020168     [COM:/acorn=5010020168]  Tesla Inc.