0

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?

Prodox21
  • 107
  • 1
  • 2
  • 9
  • 1
    That is not valid JSON. You have no quotes around the keys or the string literals and no comma between the key-value pairs. – MT0 Jun 30 '22 at 15:38
  • Hot to treat this data `{ first: one with space second:two }`? – astentx Jun 30 '22 at 16:20

2 Answers2

0

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

Massi FD
  • 360
  • 4
  • 8
0

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
;
robertus
  • 338
  • 1
  • 7