Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

Simple Json query I am having problems with

Geert01
Geert01 Member Posts: 93 Blue Ribbon

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!

Tagged:

Best Answer

«1

Answers

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon

    I have added a json file for if you want to experiment😉 with your solution

    😉

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon

    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

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon

    All I am missing is the number:


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,904 Red Diamond

    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.

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon
    edited Nov 27, 2021 4:07PM

    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.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,904 Red Diamond

    What numbers? They are key names, not numbers.

    SY.

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon
    edited Nov 27, 2021 4:46PM

    Hi Soloman,

    Whatever they are called, I need those numbers / key names to be able to turn on or off the philips hue lights.


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,904 Red Diamond
    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.

  • Geert01
    Geert01 Member Posts: 93 Blue Ribbon

    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

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,904 Red Diamond
    Accepted Answer

    Just a clob.

    SY.