1

For the below possible JSON values in a column, I need to extract value for CAT attribute Input Table

|  VALUES                                                    |
--------------------------------------------------------------
| {"ANIMALS":"1","DOG":"D1","CAT":"C1"}                      |
| {"ANIMALS":"2","DOG":"D2","CAT":"C2"}                      |
| {"ANIMALS":"3","DOG":"D3"}                                 |
| {"ANIMALS":"4","CAT":"C4", "DOG":"D4"}                     |
--------------------------------------------------------------

NOTE: There could be many other non-ordered attributes as a JSON could.

Expected result

| CAT     |
-----------
| C1      |
| C2      |
| NULL    |
| C4      |
-----------

I've been trying to use something like

SELECT REGEXP_SUBSTR('{"ANIMALS":"1","DOG":"D1","CAT":"C1"}', 'CAT[^,]+') "REGEXPR_SUBSTR" FROM DUAL;

which gives me CAT":"C1"} for the above input which doesn't fulfil my need to fetch the value.

Ramesh Kumar
  • 41
  • 1
  • 1
  • 7
  • I think it's possible to just read the value *as JSON* and then do a query on those properties, instead of using regex that has a chance to match something incorrectly. – VLAZ Apr 10 '20 at 09:42
  • I think you are correct. Why didn't I think about it. SELECT JSON_VALUE('{"ANIMALS":"1","DOG":"D1","CAT":"C1"}', '$.CAT') AS value FROM DUAL will do the trick – Ramesh Kumar Apr 10 '20 at 09:46
  • 1
    ... depending on which version of Oracle you are using; JSON support started to be added in 12c. Which version are you on? – Alex Poole Apr 10 '20 at 09:46
  • Yes, I am using 12c – Ramesh Kumar Apr 10 '20 at 09:52

1 Answers1

1

As per suggestion, JSON_VALUE will do the trick as the values is a JSON

SELECT
    JSON_VALUE('{"ANIMALS":"1","DOG":"D1","CAT":"C1"}', '$.CAT') AS value
FROM
    dual
Ergi Nushi
  • 837
  • 1
  • 6
  • 17
Ramesh Kumar
  • 41
  • 1
  • 1
  • 7