SQL : How to extract particular value from a key in data dictionary

Question

I want to extract only city values from the below given data structure using Oracle SQL query.

Ex: {"firstName":"Curtis","lastName":"C Fugatt","street1":"4146 Audiss Rd.","street2":null,"city":"Milton"}

PS: The function: json_value is not working and other threads are suggesting solution in Python. I want to use SQL only.

Answer 1

With sample data you posted, one option might be this:

SQL> with test (col) as
  2    (select '{"firstName":"Curtis","street2":null,"city":"Milton"}' from dual union all
  3     select '"lastName":"C Fugatt","street1":"4146 Audiss Rd.","city":"Los Angeles"}' from dual
  4    )
  5  select regexp_replace(substr(col, instr(col, 'city') + 7), '[^[:alnum:] ]', '') result
  6  from test;

RESULT
--------------------------------------------------------------------------------
Milton
Los Angeles

SQL>

What does it do?

• substr finds the 1st position of the city string and adds 7 to it (to skip city itself, double quotes and the colon sign) and - as a result - returns everything to the end of the string
  • it means that city must be the last info in the string
• regexp_replace removes anything but alphanumerics and spaces
• what remains is city name (i.e. the final result)

---

As you changed your mind (so that city isn't the last information in the string), one option might be a nested SUBSTR:

SQL> with test (col) as
  2    (select '{"firstName":"Curtis","street2":null,"city":"Milton"}' from dual union all
  3     select '"lastName":"C Fugatt","street1":"4146 Audiss Rd.","city":"Los Angeles"}' from dual union all
  4     select '"firstName":"Curtis","lastName":"C Fugatt","street1":"4146 Audiss Rd.","street2":null,"city":"Milton","state":"FL","zip":"32583","country":"USA","phone":"' from dual
  5    )
  6  select
  7    substr(substr(col, instr(col, 'city') + 7),                 -- everything that follows "city"
  8           1,                                                   -- starting from the 1st position
  9           instr(substr(col, instr(col, 'city') + 7), '"') - 1  -- up to the first double quote in that "everything that follows "city"" substring
 10          ) result
 11  from test;

RESULT
--------------------------------------------------------------------------------
Milton
Los Angeles
Milton

SQL>

Answer 2

If your value is JSON-like (but is not quite so you cannot parse it using JSON functions) then you can parse a JSON-like key-value pair using:

SELECT REGEXP_SUBSTR(
         value,
         '[,{]\s*"city"\s*:\s*"((\\[\/"bfnrt]|\\u[0-9a-fA-Z]{4}|[^\])*)"',
         1,
         1,
         NULL,
         1
       ) AS city
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT '{"firstName":"Curtis","lastName":"C Fugatt","street1":"4146 Audiss Rd.","street2":null,"city":"Milton"}' FROM DUAL UNION ALL
SELECT '{
  "not_this_one":
    "city",
  "big_city":
    "there",
  "little_city":
    "somewhere",
  "city":
      "here"
}' FROM DUAL;

Note: in the second example, you cannot naively look for city or "city" as it will match a value rather than a key.

Outputs:

CITY
Milton
here

---

However, if it is JSON data (which your sample is) then you can (and should) use JSON functions to parse it:

SELECT JSON_VALUE(value, '$.city') AS city
FROM   table_name;

db<>fiddle here