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) ??

What's your database version? docs.oracle.com/database/121/ADXDB/json.htm — pablomatico
– pablomatico, Commented Mar 31, 2015 at 5:58
('Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production',0); 'PL/SQL Release 12.1.0.2.0 - Production',0); 'CORE 12.1.0.2.0 Production',0); — JDOaktown
– JDOaktown, Commented Mar 31, 2015 at 6:20

Nick Pierpoint · Accepted Answer · 2015-04-30 22:45:02Z

1

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.

edited Apr 30, 2015 at 22:45

answered Apr 30, 2015 at 15:12

Nick Pierpoint

17.8k9 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

JDOaktown Over a year ago

what about the \" which comes over from the gson serialization?

JDOaktown Over a year ago

and 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) ?

JDOaktown Over a year ago

when I type: select upJSON.values from myLocations I get: SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification

Nick Pierpoint Over a year ago

Is there a check (upJSON is json) constraint on the table? Can you provide the table DDL? The ...{\"mResults\":[0.0,0.... in the formatted JSON makes me wonder if the JSON is correctly formed.

Nick Pierpoint Over a year ago

Once you've got your upJSON field returned I suppose you could parse it yourself (manually) to extract the data you require. Or transfer as a JSON record in a separate transfer table to allow parsing. First issue is finding a json_value selector that matches your data - maybe you have to go down the array - something like $values[*].nameValuePairs.id. I'm afraid I don't have a copy of 12c to hand to test on. Lots of good query examples in the link I've included in my answer. Hope I've been of some help pointing you in the right direction.

|

Nick Pierpoint · Accepted Answer · 2015-05-03 21:31:02Z

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
    ;

edited May 3, 2015 at 21:31

Nick Pierpoint

17.8k9 gold badges48 silver badges74 bronze badges

answered May 2, 2015 at 22:32

JDOaktown

4,5547 gold badges42 silver badges56 bronze badges

1 Comment

JDOaktown Over a year ago

The no_merge hint is perhaps the most elusive aspect of this answer.

Collectives™ on Stack Overflow

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

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related