JSON Oracle SQL parsing / unnest embedded JSON data in escaped form

Question

Here is my JSON stored in a CLOB column:

select upJSON from myLocations;

{"values":[
{"nameValuePairs":{"upJSON":"{\"mResults\":[0.0,0.0],\"mProvider\":\"fused\",\"mDistance\":0.0,\"mAltitude\":0.0}","id":"1","updated":"2015-03-30 20:28:51"}},
{"nameValuePairs":{"upJSON":"{\"mResults\":[0.0,0.0],\"mProvider\":\"FINDME\",\"mDistance\":0.0,\"mAltitude\":22.2}","id":"2","updated":"2015-03-30 20:28:53"}},
{"nameValuePairs":{"upJSON":"{\"mResults\":[0.0,0.0],\"mProvider\":\"fused\",\"mDistance\":0.0,\"mAltitude\":0.0}","id":"3","updated":"2015-03-30 20:28:55"}},
{"nameValuePairs":{"upJSON":"{\"mResults\":[0.0,0.0],\"mProvider\":\"fused\",\"mDistance\":0.0,\"mAltitude\":0.0}","id":"4","updated":"2015-03-30 20:28:57"}}
]}           

(I have inserted newlines for clarity)

Please: What is the SQL (or PL/SQL) needed to select just the value of mProvider, mAltitude, and the id from the 2nd "nameValuePairs" (= "FINDME" and 22.2 and "2") in the example above) ??

Answer 1

https://odieweblog.wordpress.com/2015/04/12/json_table-chaining/#comment-1025

with tmp as (
    SELECT /*+ no_merge */ d.*
    FROM ulocations ul,
         json_table(ul.upjson, '$'
         columns(
         NESTED PATH '$.values[*].nameValuePairs'
               COLUMNS (
                   updated VARCHAR2(19 CHAR)  PATH '$.updated'
                 , id varchar2(9 char)        path '$._id'
                 , upJSON VARCHAR2(2000 CHAR) PATH '$.upJSON'
                )) ) d
    --where d.id = '0'
    )
    select t.updated
        , t.id
        , jt2.*
    from tmp t
       , json_table(t.upJSON, '$'
          columns mProvider varchar2(5) path '$.mProvider'
                , mLongitude  number    path '$.mLongitude'
        ) jt2
    ;

Answer 2

Since you're using 12c you have access to the native JSON parsing (as long as your CLOB column has an is json check constraint).

Some good background is available at:

https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6371

If you're JSON looks something like this:

{
    "values": [
        {
            "nameValuePairs": {
                "upJSON": {
                    "mResults": [
                        "0.0",
                        "0.0"
                    ],
                    "mProvider": "fused",
                    "mDistance": "0.0",
                    "mAltitude": "22.2"
                },
                "id": "1",
                "updated": "2015-03-30 20:28:51"
            }
        },
        ...
        ...

Although, when I put your snippet from the question into JSONLint it returns:

{
    "values": [
        {
            "nameValuePairs": {
                "upJSON": "{\"mResults\":[0.0,0.0],\"mProvider\":\"fused\",\"mDistance\":0.0,\"mAltitude\":0.0}",
                "id": "1",
                "updated": "2015-03-30 20:28:51"
            }
        },
        {
            "nameValuePairs": {
                "upJSON": "{\"mResults\":[0.0,0.0],\"mProvider\":\"FINDME\",\"mDistance\":0.0,\"mAltitude\":22.2}",
                "id": "2",
                "updated": "2015-03-30 20:28:53"
            }
        },

Something like the following might get you started:

select
    upJSON.values
from
    myLocations
where
    json_value(upJSON, '$nameValuePairs.id' returning varchar2 error on error) = '2';

If you want to limit the query to a single ID, you'll need to add a full-text or function-based index to the JSON column.