0

While working on oracle json datatype and trying to extract data from it, not able to extract name & value elements from this. tried using all known notations but getting null.

select json_query(po_document, '$.actions.parameters[0]') from j_purchaseorder where ID='2';

sql output

dbc
  • 104,963
  • 20
  • 228
  • 340
girish chalke
  • 33
  • 8

3 Answers3

0

You can use the JSON_VALUE function as follows:

SQL> select JSON_VALUE('{"_class":"123", "name":"tejash","value":"so"}', '$.name') as name,
  2     JSON_VALUE('{"_class":"123", "name":"tejash","value":"so"}', '$.value') as value
  3  from dual;

NAME       VALUE
---------- ----------
tejash     so

SQL>
Popeye
  • 35,427
  • 4
  • 10
  • 31
0

Thanks for your help. got required output using below

    select json_value(json_query(po_document, '$.actions.parameters[0]'),'$.value') from j_purchaseorder where ID='2' and
json_value(json_query(po_document, '$.actions.parameters[0]'),'$.name') = 'SERVERUSER';
girish chalke
  • 33
  • 8
0

As explained, for example, in the Oracle documentation, multiple calls to JSON_VALUE() on the same JSON document may result in very poor performance. When we need to extract multiple values from a single document, it is often best (for performance) to make a single call to JSON_TABLE().

Here is how that would work on the provided document. First I create and populate the table, then I show the query and the output. Note the handling of column (attribute) "_class", both in the JSON document and in the SQL SELECT statement. In both cases the name must be enclosed in double-quotes, because it begins with an underscore.

create table j_purchaseorder (
    id          number primary key,
    po_document clob   check (po_document is json)
);

insert into j_purchaseorder (id, po_document) values (
2, '{"_class":"hudson.model.StringParameterValue","name":"SERVERUSER","value":"avlipwcnp04"}'
);

commit;

select "_CLASS", name, value
from   j_purchaseorder
       cross apply
       json_table(po_document, '$'
                     columns (
                               "_CLASS" varchar2(40) path '$."_class"',
                               name     varchar2(20) path '$.name',
                               value    varchar2(20) path '$.value'
                             )
                 )
where  id = 2
;

_CLASS                                   NAME               VALUE             
---------------------------------------- ------------------ ------------------
hudson.model.StringParameterValue        SERVERUSER         avlipwcnp04