Discussions
Categories
- 382.2K All Categories
- 2.1K Data
- 208 Big Data Appliance
- 1.9K Data Science
- 447.9K Databases
- 220.9K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 522 MySQL Community Space
- 465 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 490 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.1K SQL & PL/SQL
- 21K SQL Developer
- 293.2K Development
- 7 Developer Projects
- 128 Programming Languages
- 289.9K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 144 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 198 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 260 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Simple Json query I am having problems with
I am new to processing json with sql.
I have a very small json document and only need to retrieve 3 fields from every 'record' in the file.
I cannot manage to get the 'numbers' 1,2,4,5 etc into the result set.
I need the number (1) for the first record and (4) for the third record and from the 'records; I also need the name fields.
This should be pretty simple, but unfortunately not simple for me.
Any help would be very much appreciated!
Best Answer
-
Just a clob.
SY.
Answers
-
I have added a json file for if you want to experiment😉 with your solution
😉
-
And to make it even easier, here is a query that has the json data embeded.
SELECT jt.*
FROM (select
'{"1":{"state":{"on":false,"bri":143,"hue":7675,"sat":199,"effect":"none","xy":[0.5016,0.4151],"ct":443,"alert":"none","colormode":"xy","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-06T12:19:06"},"type":"Extended color light","name":"Lamp in hoek","modelid":"LCT015","manufacturername":"Signify Netherlands B.V.","productname":"Hue color lamp","capabilities":{"certified":true,"control":{"mindimlevel":1000,"maxlumen":806,"colorgamuttype":"C","colorgamut":[[0.6915,0.3083],[0.17,0.7],[0.1532,0.0475]],"ct":{"min":153,"max":500}},"streaming":{"renderer":true,"proxy":true}},"config":{"archetype":"sultanbulb","function":"mixed","direction":"omnidirectional","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:03:9c:5b:2c-0b","swversion":"1.88.1","swconfigid":"76B74E79","productid":"Philips-LCT015-1-A19ECLv5"},"2":{"state":{"on":false,"bri":143,"hue":7675,"sat":199,"effect":"none","xy":[0.5016,0.4151],"ct":443,"alert":"select","colormode":"xy","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-06T12:19:16"},"type":"Extended color light","name":"Lamp TV","modelid":"LCT015","manufacturername":"Signify Netherlands B.V.","productname":"Hue color lamp","capabilities":{"certified":true,"control":{"mindimlevel":1000,"maxlumen":806,"colorgamuttype":"C","colorgamut":[[0.6915,0.3083],[0.17,0.7],[0.1532,0.0475]],"ct":{"min":153,"max":500}},"streaming":{"renderer":true,"proxy":true}},"config":{"archetype":"sultanbulb","function":"mixed","direction":"omnidirectional","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:03:89:2c:82-0b","swversion":"1.88.1","swconfigid":"76B74E79","productid":"Philips-LCT015-1-A19ECLv5"},"4":{"state":{"on":false,"bri":143,"hue":7675,"sat":199,"effect":"none","xy":[0.5016,0.4151],"ct":443,"alert":"select","colormode":"xy","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-06T12:19:14"},"type":"Extended color light","name":"Lamp Tafel","modelid":"LCT015","manufacturername":"Signify Netherlands B.V.","productname":"Hue color lamp","capabilities":{"certified":true,"control":{"mindimlevel":1000,"maxlumen":806,"colorgamuttype":"C","colorgamut":[[0.6915,0.3083],[0.17,0.7],[0.1532,0.0475]],"ct":{"min":153,"max":500}},"streaming":{"renderer":true,"proxy":true}},"config":{"archetype":"sultanbulb","function":"mixed","direction":"omnidirectional","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:04:fd:0f:99-0b","swversion":"1.88.1","swconfigid":"76B74E79","productid":"Philips-LCT015-1-A19ECLv5"},"5":{"state":{"on":false,"bri":143,"hue":7675,"sat":199,"effect":"none","xy":[0.5016,0.4151],"ct":443,"alert":"none","colormode":"xy","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-06T12:19:08"},"type":"Extended color light","name":"Lightstrip","modelid":"LST002","manufacturername":"Signify Netherlands B.V.","productname":"Hue lightstrip plus","capabilities":{"certified":true,"control":{"mindimlevel":40,"maxlumen":1600,"colorgamuttype":"C","colorgamut":[[0.6915,0.3083],[0.17,0.7],[0.1532,0.0475]],"ct":{"min":153,"max":500}},"streaming":{"renderer":true,"proxy":true}},"config":{"archetype":"huelightstrip","function":"mixed","direction":"omnidirectional","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:03:ce:1d:6d-0b","swversion":"1.88.1","swconfigid":"8EF6FE80","productid":"Philips-LST002-1-LedStripsv3"},"6":{"state":{"on":false,"bri":143,"hue":7675,"sat":199,"effect":"none","xy":[0.5016,0.4151],"ct":443,"alert":"none","colormode":"xy","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-06T12:19:11"},"type":"Extended color light","name":"Lamp bij deur","modelid":"LCT015","manufacturername":"Signify Netherlands B.V.","productname":"Hue color lamp","capabilities":{"certified":true,"control":{"mindimlevel":1000,"maxlumen":806,"colorgamuttype":"C","colorgamut":[[0.6915,0.3083],[0.17,0.7],[0.1532,0.0475]],"ct":{"min":153,"max":500}},"streaming":{"renderer":true,"proxy":true}},"config":{"archetype":"sultanbulb","function":"mixed","direction":"omnidirectional","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:04:3e:3f:5e-0b","swversion":"1.88.1","swconfigid":"76B74E79","productid":"Philips-LCT015-1-A19ECLv5"},"7":{"state":{"on":false,"bri":38,"alert":"select","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-09T12:43:27"},"type":"Dimmable light","name":"Spot one","modelid":"LWG004","manufacturername":"Signify Netherlands B.V.","productname":"Hue white spot","capabilities":{"certified":true,"control":{"mindimlevel":2000,"maxlumen":350},"streaming":{"renderer":false,"proxy":false}},"config":{"archetype":"spotbulb","function":"functional","direction":"downwards","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:09:2f:22:69-0b","swversion":"1.88.2","swconfigid":"7F8E7654","productid":"Philips-LWG004-3-GU10DLv2"},"8":{"state":{"on":false,"bri":168,"alert":"select","mode":"homeautomation","reachable":true},"swupdate":{"state":"noupdates","lastinstall":"2021-08-09T12:43:57"},"type":"Dimmable light","name":"Spot two","modelid":"LWG004","manufacturername":"Signify Netherlands B.V.","productname":"Hue white spot","capabilities":{"certified":true,"control":{"mindimlevel":2000,"maxlumen":350},"streaming":{"renderer":false,"proxy":false}},"config":{"archetype":"spotbulb","function":"functional","direction":"downwards","startup":{"mode":"powerfail","configured":true}},"uniqueid":"00:17:88:01:09:2d:b7:c5-0b","swversion":"1.88.2","swconfigid":"7F8E7654","productid":"Philips-LWG004-3-GU10DLv2"},"9":{"state":{"on":false,"bri":1,"alert":"select","mode":"homeautomation","reachable":true},"swupdate":{"state":"notupdatable","lastinstall":"2021-09-26T19:38:45"},"type":"Dimmable light","name":"Dimmable light 1 in kast","modelid":"TRADFRI Driver 30W","manufacturername":"IKEA of Sweden","productname":"Dimmable light","capabilities":{"certified":false,"control":{},"streaming":{"renderer":false,"proxy":false}},"config":{"archetype":"ceilinground","function":"functional","direction":"omnidirectional"},"uniqueid":"5c:02:72:ff:fe:70:5b:a7-01","swversion":"1.2.245"}}' json_data from dual) t
, JSON_TABLE (json_data, '$.*'
COLUMNS (
name VARCHAR2(50)
, productname VARCHAR2(30) PATH '$.productname'
, nested state[*] columns
( hue
, sat
, effect
, "on"
)
)
) AS jt
-
All I am missing is the number:
-
Oracle version? I get all on 12.2:
with json_test as (select to_clob(' { "1": { "state": { "on": true, "bri": 143, "hue": 7675, "sat": 199, "effect": "none", "xy": [ 0.5016, 0.4151 ], "ct": 443, "alert": "none", "colormode": "xy", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-06T12:19:06" }, "type": "Extended color light", "name": "Lamp in hoek", "modelid": "LCT015", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue color lamp", "capabilities": { "certified": true, "control": { "mindimlevel": 1000, "maxlumen": 806, "colorgamuttype": "C", "colorgamut": [ [ 0.6915, 0.3083 ], [ 0.17, 0.7 ], [ 0.1532, 0.0475 ] ], "ct": { "min": 153, "max": 500 } }, "streaming": { "renderer": true, "proxy": true } }, "config": { "archetype": "sultanbulb", "function": "mixed", "direction": "omnidirectional", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "22:24:78:01:03:aa:d3:5d-0b", "swversion": "1.88.1", "swconfigid": "6796AB84E89", "productid": "Philips-LCT015-1-A19ECLv5" },') || ' "2": { "state": { "on": true, "bri": 143, "hue": 7675, "sat": 199, "effect": "none", "xy": [ 0.5016, 0.4151 ], "ct": 443, "alert": "select", "colormode": "xy", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-06T12:19:16" }, "type": "Extended color light", "name": "Lamp TV", "modelid": "LCT015", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue color lamp", "capabilities": { "certified": true, "control": { "mindimlevel": 1000, "maxlumen": 806, "colorgamuttype": "C", "colorgamut": [ [ 0.6915, 0.3083 ], [ 0.17, 0.7 ], [ 0.1532, 0.0475 ] ], "ct": { "min": 153, "max": 500 } }, "streaming": { "renderer": true, "proxy": true } }, "config": { "archetype": "sultanbulb", "function": "mixed", "direction": "omnidirectional", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "00:22:58:11:44:81:2c:83-0a", "swversion": "1.88.1", "swconfigid": "86A74F99", "productid": "Philips-LCT015-1-A19ECLv5" },' || ' "4": { "state": { "on": true, "bri": 143, "hue": 7675, "sat": 199, "effect": "none", "xy": [ 0.5016, 0.4151 ], "ct": 443, "alert": "select", "colormode": "xy", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-06T12:19:14" }, "type": "Extended color light", "name": "Lamp Tafel", "modelid": "LCT015", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue color lamp", "capabilities": { "certified": true, "control": { "mindimlevel": 1000, "maxlumen": 806, "colorgamuttype": "C", "colorgamut": [ [ 0.6915, 0.3083 ], [ 0.17, 0.7 ], [ 0.1532, 0.0475 ] ], "ct": { "min": 153, "max": 500 } }, "streaming": { "renderer": true, "proxy": true } }, "config": { "archetype": "sultanbulb", "function": "mixed", "direction": "omnidirectional", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "00:27:98:01:53:ea:0f:98-0c", "swversion": "1.88.1", "swconfigid": "86C74e79", "productid": "Philips-LCT015-1-A19ECLv5" },' || ' "5": { "state": { "on": false, "bri": 143, "hue": 7675, "sat": 199, "effect": "none", "xy": [ 0.5016, 0.4151 ], "ct": 443, "alert": "none", "colormode": "xy", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-06T12:19:08" }, "type": "Extended color light", "name": "Lightstrip", "modelid": "LST002", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue lightstrip plus", "capabilities": { "certified": true, "control": { "mindimlevel": 40, "maxlumen": 1600, "colorgamuttype": "C", "colorgamut": [ [ 0.6915, 0.3083 ], [ 0.17, 0.7 ], [ 0.1532, 0.0475 ] ], "ct": { "min": 153, "max": 500 } }, "streaming": { "renderer": true, "proxy": true } }, "config": { "archetype": "huelightstrip", "function": "mixed", "direction": "omnidirectional", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "10:27:58:34:45:ce:1d:6e-0q", "swversion": "1.88.1", "swconfigid": "8EF6FE80", "productid": "Philips-LST002-1-LedStripsv3" },' || ' "6": { "state": { "on": true, "bri": 143, "hue": 7675, "sat": 199, "effect": "none", "xy": [ 0.5016, 0.4151 ], "ct": 443, "alert": "none", "colormode": "xy", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-06T12:19:11" }, "type": "Extended color light", "name": "Lamp bij deur", "modelid": "LCT015", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue color lamp", "capabilities": { "certified": true, "control": { "mindimlevel": 1000, "maxlumen": 806, "colorgamuttype": "C", "colorgamut": [ [ 0.6915, 0.3083 ], [ 0.17, 0.7 ], [ 0.1532, 0.0475 ] ], "ct": { "min": 153, "max": 500 } }, "streaming": { "renderer": true, "proxy": true } }, "config": { "archetype": "sultanbulb", "function": "mixed", "direction": "omnidirectional", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "00:22:19:26:16:3e:3f:5e-0b", "swversion": "1.88.1", "swconfigid": "77B84E97", "productid": "Philips-LCT015-1-A19ECLv5" },' || ' "7": { "state": { "on": false, "bri": 38, "alert": "select", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-09T12:43:27" }, "type": "Dimmable light", "name": "Spot one", "modelid": "LWG004", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue white spot", "capabilities": { "certified": true, "control": { "mindimlevel": 2000, "maxlumen": 350 }, "streaming": { "renderer": false, "proxy": false } }, "config": { "archetype": "spotbulb", "function": "functional", "direction": "downwards", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "00:27:88:33:09:2fg:12:76-0b", "swversion": "1.88.2", "swconfigid": "7F8E78977", "productid": "Philips-LWG004-3-GU10DLv2" },' || ' "8": { "state": { "on": false, "bri": 168, "alert": "select", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "noupdates", "lastinstall": "2021-08-09T12:43:57" }, "type": "Dimmable light", "name": "Spot two", "modelid": "LWG004", "manufacturername": "Signify Netherlands B.V.", "productname": "Hue white spot", "capabilities": { "certified": true, "control": { "mindimlevel": 2000, "maxlumen": 350 }, "streaming": { "renderer": false, "proxy": false } }, "config": { "archetype": "spotbulb", "function": "functional", "direction": "downwards", "startup": { "mode": "powerfail", "configured": true } }, "uniqueid": "00:17:99:01:09:2h:b7:c8-0b", "swversion": "1.88.2", "swconfigid": "7F8E76744", "productid": "Philips-LWG004-3-GU10DLv2" },' || ' "9": { "state": { "on": true, "bri": 1, "alert": "select", "mode": "homeautomation", "reachable": true }, "swupdate": { "state": "notupdatable", "lastinstall": "2021-09-26T19:38:45" }, "type": "Dimmable light", "name": "Dimmable light 1 in kast", "modelid": "TRADFRI Driver 30W", "manufacturername": "IKEA of Sweden", "productname": "Dimmable light", "capabilities": { "certified": false, "control": {}, "streaming": { "renderer": false, "proxy": false } }, "config": { "archetype": "ceilinground", "function": "functional", "direction": "omnidirectional" }, "uniqueid": "9c:03:72:gg:ft:71:5b:a7-01", "swversion": "1.2.245" } }' json_data from dual ) select jt.* from json_test t, json_table( json_data, '$.*' columns( name varchar2(30) PATH '$.name', productname varchar2(30) PATH '$.productname' ) ) jt / NAME PRODUCTNAME ------------------------------ ------------------------------ Lamp in hoek Hue color lamp Lamp TV Hue color lamp Lamp Tafel Hue color lamp Lightstrip Hue lightstrip plus Lamp bij deur Hue color lamp Spot one Hue white spot Spot two Hue white spot Dimmable light 1 in kast Dimmable light 8 rows selected. SQL>SY.
-
I am missing the numbers 1,2,4,5,6,7,8 and 9. Notice that number 3 is missing. These numbers are the problem. I do not know how to get those into the output.
-
What numbers? They are key names, not numbers.
SY.
-
Hi Soloman,
Whatever they are called, I need those numbers / key names to be able to turn on or off the philips hue lights.
-
with t as ( select json_data, (select json_dataguide(json_data,dbms_json.format_hierarchical) from dual) json_data_guide from json_test ) select jt1.key_name, jt2.name, jt2.productname from t, json_table( json_data_guide, '$.properties.*' columns( key_name varchar2(30) path '$."o:preferred_column_name"', n for ordinality ) ) jt1, json_table( json_data, '$.*' columns( name varchar2(25) PATH '$.name', productname varchar2(25) PATH '$.productname', n for ordinality ) ) jt2 where jt2.n = jt1.n / KEY_NAME NAME PRODUCTNAME ---------- ------------------------- ------------------------- 1 Lamp in hoek Hue color lamp 2 Lamp TV Hue color lamp 4 Lamp Tafel Hue color lamp 5 Lightstrip Hue lightstrip plus 6 Lamp bij deur Hue color lamp 7 Spot one Hue white spot 8 Spot two Hue white spot 9 Dimmable light 1 in kast Dimmable light 8 rows selected. SQL>
SY.
-
Looks great Solomon!
The table you inserted the json data into, does that have special type columns? Or is it just a clob column?
If it is a special type could you tell me how to insert the data into the table?
I very much appreciate your help! Thank so much!
Geert
-
Just a clob.
SY.