extract value from json in oracle

Question

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.

Answer 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