Split json field to extract value in oracle sql

Question

I have a table with a varchar2 type field in which a simil "json" like this is saved:

json data{
     first:one
     second:two
}

the string "json" present in the example above is also saved in the field.

I need a query that brings out the values ​​"one", "two". Could you help me?

Answer 1

In Oracle this sql statement with table and data field:

--couple
select regexp_substr(data, '\S+', 1, 3) as item1 from table;
select regexp_substr(data, '\S+', 1, 4) as item2 from table;

--values
select regexp_substr(regexp_substr(data, '\S+', 1, 3), '[^:]+', 1, 2) as value1 from table;
select regexp_substr(regexp_substr(data, '\S+', 1, 4), '[^:]+', 1, 2) as value2 from table;

Thank you

Answer 2

This is not a correct JSON, so you're unable to use Oracle's builtin functions for processing json data. Maybe you could alter text to be a correct JSON but in my opinion there is a more simple solution.

If this text has a fixed structure where every value is in a new line and preceded by key:, then you could use:

• regular expressions: regexp_substr (for extracting each key:value line) then regexp_replace to get value only
• connect by to get recursively all values.

Look at the example below:

with mytable as (
    select 'json data{'||chr(10)||
           '         first:one'||chr(10)||
           '         second:two'||chr(10)||
           '         third:three'||chr(10)||
           '    }' as json_data_col
    from dual
)
select (regexp_replace(regexp_substr(json_data_col, '^(.+:){1}(.*)$', 1, level, 'm'), '(^.*:)(.*)($)', '\2', 1, 1)) as extracted_value
from mytable
connect by regexp_substr(json_data_col, '^(.+:){1}(.*)$', 1, level, 'm') is not NULL
;