Get a property from a JSON which is stored in a clob column-CodePudding

I have a clob column in my table, in which I store json string. the data in the column is something like below:

{"date":"2021/11/11", "name":"test","errorCode":"00000","type":"test"}

I want to write a select query to get the name. Any suggestions?

CodePudding user response：

Assuming you're on 12c or higher, you can extract the value using json_value:

select json_value ( 
         '{"date":"2021/11/11", "name":"test","errorCode":"00000","type":"test"}',
         '$.name'
       ) nm
from   dual;

NM     
test

CodePudding user response：

One option is using JSON_TABLE provided your DB version is 12c (12.1.0.2) such as

SELECT jt.*
  FROM t,
       JSON_TABLE(jscol,
                  '$'                  
                  COLUMNS(
                           name  VARCHAR2(100) PATH '$.name',
                          "date" VARCHAR2(100) PATH '$.date' 
                          )
                  ) jt

this way, you can pick all the columns listed after COLUMNS keyword

Demo