SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get all distinct attributes names from JSON docs stored in Oracle database using SQL?

BilalFeb 16 2021

Hi All,
Oracle Database 19c.
I want to verify the column names used in all JSON rows stored in a table. Is there a way to extract all attribute names from a JSON document using Oracle SQL statement?
Below is an example JSON document.

 {
  "timestamp": "2021-02-16T15:42:41Z",
  "source" : "Telit",
  "device_uid": "353081091868151",
  "latitude": "51.63806406",
  "longitude": "-2.67872611",
  "fix_type": "lwm2m-gps",
  "pollution": {
            "NO2" : "66.55805206298828",
            "CO2" :" 575",
            "PM2_5" : "4.247610569000244",
            "PM10" : "5.325907230377197"
  },
  "additional" : {
            "temperature": "9.022140502929688",
            "humidity": "93.96820068359375", 
            "pressure": "998.300048828125"
  } 
}

The result shall be

timestamp
source
device_uid
latitude
longitude
fix_type
pollution
NO2
CO2
PM2_5
PM10
additional
temperature
humidity
pressure

Any guidance will be very much appreciated.
Many Thanks and
Kind Regards,
Bilal

This post has been answered by mathguy on Feb 17 2021

Jump to Answer

Added on Feb 16 2021

4 comments

3,149 views