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: As this is not a JSON data structure, json_value is not working and other threads are suggesting solution in Python. I want to use SQL only.

CodePudding user response：

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)

CodePudding user response：

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