CODEWITHSUNDEEP
sqloracle-database
Jim Macaulay
Jim Macaulay

Reputation: 5165

Reverse Substring the value with the position

I have data

col1
[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]
[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]

and I wanted to extract a value

value -> White
value -> Black

select col1, instr('key -> Colour' in col1), substr(col1, instr('key -> Colour')
from 
(SELECT '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
union all
SELECT '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
) tbl;

I have tried using position and substring, but I am unable to get. What can I try next?

Basically I need to search for a value key -> Colour and extract the value by reverse substring till { which will get me the value value -> White and value -> Black

Upvotes: 1

Views: 65

Answers (3)

MT0
MT0

Reputation: 168470

You can use REGEXP_SUBSTR to find the match:

SELECT REGEXP_SUBSTR(
         col1,
         '\{(key -> Colour, )?(value -> [^},]*?)(, key -> Colour)?\}',
         1,
         1,
         NULL,
         2
       ) AS colour
FROM   tbl

Which, for the sample data:

CREATE TABLE tbl (col1) AS
SELECT EMPTY_CLOB() || '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' from dual union all
SELECT EMPTY_CLOB() || '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' from dual;

Outputs:

COLOUR
value -> White
value -> Black

Alternatively, do not try to encode your own storage format. If you are going to use something that bears a passing resemblance to the JSON format then actually use the JSON format rather that an approximation.

UPDATE tbl
SET col1 = REGEXP_REPLACE(
             col1,
             '\{value -> (.*?), key -> (.*?)\}',
             '{"value": "\1", "key": "\2"}'
           )

Then you can use a proper JSON parser:

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$[*]'
         COLUMNS (
           key_name VARCHAR2(20) PATH '$.key',
           value    VARCHAR2(20) PATH '$.value'
         )
       ) j
WHERE  key_name = 'Colour'

Which outputs:

KEY_NAME VALUE
Colour White
Colour Black

or:

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$'
         COLUMNS (
           fruit  VARCHAR2(20) PATH '$[*]?(@.key=="Fruit").value',
           colour VARCHAR2(20) PATH '$[*]?(@.key=="Colour").value'
         )
       ) j

Which outputs:

FRUIT COLOUR
Apple White
Mango Black

fiddle

Upvotes: 2

p3consulting
p3consulting

Reputation: 4640

If you want to be less dependent on the order of "k -> v" pairs, you need to go further in the parsing with regexp:

with data(id, str) as (
    SELECT 1, '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
    union all
    SELECT 2, '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
    union all
    SELECT 3, '[{key -> Fruit, value -> Mango}, {key -> Colour, value -> Black}]' as col1 from dual
),
keyvalues as (
    select id, level as grp,
        regexp_replace(replace(regexp_substr(lst, '[^}]+', 1, level),'{',''), '^,\s+', '')
        as keyvalues
    from (
        select 
            id, regexp_replace(str, '\[(.*)\]', '\1') as lst
        from data 
    )
    connect by id = prior id and level <= regexp_count(lst, '}') and prior sys_guid() is not null
),
pairs as (
    select id, idx, grp, keyval,
        substr(keyval, 1, instr(keyval,' ->') - 1) as key, 
        substr(keyval, instr(keyval,' -> ') + length(' -> ')) as value 
    from (
        select id, level as idx,grp, trim( ' ' from regexp_substr(keyvalues, '[^,]+', 1, level)) as keyval
        from keyvalues
        connect by id = prior id and grp = prior grp and
            regexp_substr(keyvalues, '[^,]+', 1, level) is not null and prior sys_guid() is not null
    )
)
select v.id, v.keyval 
from pairs k
join pairs v on k.id = v.id and k.grp = v.grp and k.idx <> v.idx 
    and v.key = 'value' 
where k.value = 'Colour'
;

Upvotes: 1

samhita
samhita

Reputation: 3505

You can start the search with value -> <value> ending with key -> Colour using REGEXP_SUBSTR, and then replace the ,key -> Colour using REGEXP_REPLACE

select col1, 
REGEXP_REPLACE
(REGEXP_SUBSTR
(col1, 'value -> [^,]*, key -> Colour'), ', key -> Colour') AS new_val

from 
(SELECT '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
union all
SELECT '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
) tbl;

Fiddle

COL1 NEW_VAL
[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}] value -> White
[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}] value -> Black

Upvotes: 1

Related Questions