Question 268326 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – How to get a value based on a key from KeyValuePair data stored in one cell in oracle database

lets say i have a table like that

id name             address                     Doc
1  {1:mark,2:john}  {1:Home,2:Work,3:Club}      {NI:299,Pass:A159}
2  {1:Max,2:Mo}     {1:Home}                    {NI:300011}

what i want is to write a qurey to select a value from cell based on the key
for example

i want the value which has key = 2 in column named(Name) where id=1

so it should return

john

and so on.
so how can i do something like that without using substring?

Tags:

3

Answers


  1. 0

    Assuming you have a valid json values :

    CREATE TABLE mytable (
  id int,
  name  VARCHAR2 (200) ,          
  address VARCHAR2 (200),                    
  Doc VARCHAR2 (200),
  CONSTRAINT ensure_json CHECK (name IS JSON)
);

insert into mytable 
select 1,  '{"1":"mark","2":"john"}', '{"1":"Home","2":"Work","3":"Club"}', '{"NI":299,"Pass":"A159"}' from dual union all
select 2,  '{"1":"Max","2":"Mo"}', '{"1":"Home"}', '{"NI":300011}' from dual

    The query can be :

    select t.name."2"
from mytable t
where id = 1

    Demo here

    If there are only key-value values in a non-valid JSON, then:

    with cte as (
  select REGEXP_REPLACE(name,
                 '([a-zA-Z0-9-]+):([a-zA-Z0-9-]+)',
                 '"1":"2"') as name
  from mytable t
  where id = 1
)
select json_value(name, '$."2"')
from cte

    Demo here

    Login or Signup to reply.
  2. 0

    If your data is as shown and isn’t JSON (or quoted) then you could use regexp_substr() to find the match, with a pattern that embeds the key value you want:

    select regexp_substr(name, '({|,)' || 2 || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = 1
    RESULT
    john

    Your script will need to supply the first 2 in the first line, and the 1 in the third line; so more generically with bind variables:

    select regexp_substr(name, '({|,)' || :p_key || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = :p_id

    The pattern is looking for { or ,, followed by your key value, followed by a colon :; and capturing everything up to another , or }. The other arguments are there to tell it to return the second capture group (the second 2 in that line). The first group is the ‘or’ of ({|,), and you want the second group (.*?) which is the value for that pair.

    By varying the key value you get different results; with the same column and 1:

    select regexp_substr(name, '({|,)' || 1 || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = 1
    RESULT
    mark

    And you can use the same approach with the other key/value columns.

    fiddle

    Of course, it would be better to store the data relationally, not as key/value pairs within a string column.

    Login or Signup to reply.
  3. 0

    Your name column is not valid JSON so you cannot use JSON functions.

    You could use regular expressions but they are slower than simple string functions. So, even though you said you do not want to use SUBSTR, you should use SUBSTR:

    SELECT id,
       CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name2
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1, 1) + 3
         ) AS epos
  FROM   table_name
)

    Which, for the sample data:

    CREATE TABLE table_name (id, name) AS
SELECT 1, '{1:mark,2:john}' FROM DUAL UNION ALL
SELECT 2, '{1:Max,2:Mo}'    FROM DUAL;

    Outputs:

    ID NAME2
    1 john
    2 Mo

    If you only want when id = 1 then add that filter and if you want to make the index you are matching a more dynamic parameter then:

    SELECT CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1, 1) + 3
         ) AS epos
  FROM   table_name
         CROSS JOIN (SELECT 2 AS index_to_match FROM DUAL)
  WHERE  id = 1
)

    Or use a parameterised query and a bind parameter (instead of a sub-query).

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search