Quick JSON Query Tip: Use Column Aliases for Arrays of Objects

加入dbDao.com Oracle技术学习QQ群:171092051

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 400-690-3643 备用电话: 13764045638 邮箱:service@parnassusdata.com

 

This isn’t a SQL Developer post, sorry folks.

When it takes me more than a couple of Google searches and more than a little bit of poking around to figure something out, I reckon it’s blog-post-worthy. Maybe I can save you a bit of time too!

I have:

  • a 12.1.0.2 database
  • the JSON patchset
  • a table
  • said table with IS JSON CHECK constraint
  • some json docs that contain nested arrays

So it looks a little something like this:

The json data has a record in it and then some nested records too...

The json data has a record in it and then some nested records too…

So you can play along at home:

 
CREATE TABLE "HR"."OPENDATA"
  (
    "NAME"         VARCHAR2(512),
    "AMENITY"      VARCHAR2(256),
    "ID"           NUMBER(11,0) NOT NULL ENABLE,
    "CONTRIBUTOR"  VARCHAR2(100),
    "VISIBLE"      VARCHAR2(26),
    "SOURCE"       VARCHAR2(128),
    "OTHER_TAGS"   VARCHAR2(4000),
    "GEO_POINT_2D" VARCHAR2(50),
    "WHEN"         TIMESTAMP (6) WITH TIME ZONE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "USERS" ;
ALTER TABLE "HR"."OPENDATA" ADD CONSTRAINT "IS_JSON" CHECK (other_tags
IS
  JSON) ENABLE;
ALTER TABLE "HR"."OPENDATA" ADD CONSTRAINT "OPENDATA_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE;
 
 
REM INSERTING INTO OPENDATA
SET DEFINE OFF;
INSERT INTO OPENDATA (NAME,AMENITY,ID,CONTRIBUTOR,VISIBLE,SOURCE,OTHER_TAGS,GEO_POINT_2D,WHEN) VALUES ('Santo Niño Church','place_of_worship',357598883,'Geogast',NULL,NULL,'{''religion'': ''christian'',
  ''gnis:state_id'': ''35'',
  ''gnis:created'': ''11/13/1980'',
  ''gnis:feature_id'': ''910806'',
  ''gnis:county_id'': ''047'',
   ''ele'': ''1994'', 
  "comments": [
     { "CommentDate":"2015-08-10 13:13:22",  "Words":"Here" },
     { "CommentDate":"2015-08-11 13:23:22",  "Words":"There"}
    ]
}','35.5092106, -105.2622308',to_timestamp_tz('07-FEB-10 11.37.21.000000000 AM -05:00','DD-MON-RR HH.MI.SSXFF AM TZR'));

The JSON team here at Oracle has made it pretty darn easy to query stuff out of my OTHER_TAGS column. I can simply use DOT notation to reference any of the JSON document attributes.

So..

j.other_tags.religion, j.other_tags.denomination - columnDOTattribute to get the data

j.other_tags.religion, j.other_tags.denomination – columnDOTattribute to get the data

So those scalar values are easy to get. How hard is for me to get that ‘comments’ stuff?

Well, I could get all of it:

Interesting, but useful?

Interesting, but useful?

I just want to ‘split’ the the nested comment data for that record into dates and the text, so that must be easy, right?

Do what?

Do what?

So it turns out this works just fine as long as you’re only asking for ONE of those nested values. But if you want MORE than one, it becomes ‘ambiguous.’

To fix this, use a column alias.

And I found this in the [Docs]:

The dot-notation syntax is a table alias followed by a dot (.), the name of a JSON column, and one or more pairs of the form . json_object_key. (Note that the table alias is mandatory.)

So let’s try that query again:

SELECT name,
       amenity,
       nvl(j.other_tags.religion, 'unkown') religion,
       nvl(j.other_tags.denomination, 'unkown') denomination,
       j.other_tags.comments.CommentDate AS "Comment Date",
       j.other_tags.comments.Words AS "Comments"
FROM opendata j
fetch FIRST 10 ROWS ONLY;
Note if the attribute you are querying doesn't exist, you just get a NULL returned for that value

Note if the attribute you are querying doesn’t exist, you just get a NULL returned for that value

A Really Nice Post on this JSON DOT Notation Syntax/Feature

Gerald Venzi has a series of posts talking about our 12c JSON support. This one in particular is of interest, and it includes better sample code and data than what I have provided.

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569