I'm trying to parse out a the [confirmation_code] value in the following json data. Is there a way I can quickly do this using a feature in Oracle 12c?
stdClass Object
(
[1] => stdClass Object
(
[1] => Array
(
)
)
[2] => stdClass Object
(
[1] => stdClass Object
(
[submission_site] => 1
[submission_code] => 111111
)
)
[3] => stdClass Object
(
[1] => {"name":"file.xml","type":"text\/xml","tmp_name":"\/tmp\/phpKkAxJI","error":0,"size":715,"mime_type":"application\/xml","virus_scan_success":true,"virus_scan_message":"Virus scan success.","error_message":"[{\"priority\":1,\"message\":\"Saved successfully\"}]","clob_id":"99999"}
)
[4] => stdClass Object
(
[1] => stdClass Object
(
[confirmation_code] => JDY7RC
)
)
)
That doesn't look too much like JSON to me ? besides the inner bit "{name: ... etc}". Looks more like PHP.
Anyway...you can parse out pieces of information from JSON in 12c using a variety of means.
Here's an example using JSON_TABLE taken from the JSON team's blog
https://blogs.oracle.com/jsondb SQL> CREATE TABLE fb_tab (col CLOB, constraint json_con_1 CHECK (col IS JSON));
Table created.
SQL>
SQL> INSERT INTO fb_tab VALUES('
2 {
3 "data": [
4 {
5 "from": {
6 "category": "Computers/technology",
7 "name": "Oracle",
8 },
9 "message": "How are Baxters Food Group and Elsevier taking their businesses",
10 "link": "http://onforb.es/1JOki7X",
11 "name": "Forbes: How The Cloud Answers Two Businesses'' Need For Speed",
12 "description": "Cloud computing can support a company''s speed and agility, ...",
13 "type": "link",
14 "created_time": "2015-05-12T16:26:12+0000",
15 "likes": {
16 "data": [
17 {
18 "name": "Starup Haji"
19 },
20 {
21 "name": "Elaine Dala"
22 }
23 ]
24 }
25 },
26 {
27 "from": {
28 "category": "Computers/technology",
29 "name": "Oracle",
30 },
31 "message": "It''s important to have an IT approach that not only red...",
32 "link": "http://www.forbes.com/sites/oracle/2015/05/07/3-ways-you-can-avoid-sp...",
33 "name": "Forbes: 3 Ways You Can Avoid Spending Too Much On IT",
34 "description": "Oracle''s suite of SaaS applications not only reduces costs but...",
35 "type": "link",
36 "created_time": "2015-05-11T19:23:11+0000",
37 "shares": {
38 "count": 5
39 },
40 "likes": {
41 "data": [
42 {
43 "name": "Asal Alibiga"
44 },
45 {
46 "name": "Hasan Reni"
47 }
48 ]
49 },
50 "comments": {
51 "data": [
52 {
53 "from": {
54 "name": "Cesar Sanchez"
55 },
56 "message": "Thanks for this information",
57 "created_time": "2015-05-12T02:52:09+0000",
58 "like_count": 1
59 }
60 ]
61 }
62 }
63 ]
64 }
65 ');
1 row created.
SQL>
SQL> SELECT jt.*
2 FROM fb_tab,
3 JSON_TABLE(col, '$.data[*]'
4 COLUMNS (
5 "Message" PATH '$.message',
6 "Type" VARCHAR2(20)PATH '$.type',
7 "ShareCount" NUMBER PATH '$.shares.count' DEFAULT 0 ON ERROR,
8 "HasComments" NUMBER EXISTS PATH '$.comments',
9 "Comments" VARCHAR2(4000) FORMAT JSON PATH '$.comments'
10 )) "JT";
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
TYPE SHARECOUNT HASCOMMENTS
-------------------- ---------- -----------
COMMENTS
----------------------------------------------------------------------------------------------------------------------------------
How are Baxters Food Group and Elsevier taking their businesses
link 0
It's important to have an IT approach that not only red...
link 5 1
{"data":[{"from":{"name":"Cesar Sanchez"},"message":"Thanks for this information","created_time":"2015-05-12T02:52:09+0000","like_
count":1}]}
SQL> col message format a40 trunc
SQL> col comments format a40 trunc
SQL> set lines 200
SQL> /
MESSAGE TYPE SHARECOUNT HASCOMMENTS COMMENTS
---------------------------------------- -------------------- ---------- ----------- ----------------------------------------
How are Baxters Food Group and Elsevier link 0
It's important to have an IT approach th link 5 1 {"data":[{"from":{"name":"Cesar Sanchez"
Some simpler examples can also be found on Tim Hall's site
https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1