Reading JSON data from BLOB

Question

Edit - oracle version 19c

I am uploading a json file using Browse file type in APEX and then storing it in a table as BLOB. The Table looks like this -

File_ID    Filename             Mime_type          created_on      blob_content
   1       file_new.json     application/json       9/1/2020           (BLOB)

Now i want to parse this and read the contents of blob as a table in oracle. How can i do it?

The Json file looks like this but has hundred of rows.

[{"Id":"50021","eName":"random123", "Type":"static","Startdate":"07/03/2020","Enddate":"08/02/2020,"nominations":[{"nominationId":"152","nominationMaxCount":7500,"offer":[{"Id":"131","Type":"MONEY","clientId":41,
"stateExclusions":[],"divisionInclusions":["111","116","126","129"]]}]

so you want to see Id, eName, Type, Startdate...as columns, and each of those show as a column in a row for your SQL? I'm assuming you want the nominations array as some sort of nested column as well? — thatjeffsmith
– thatjeffsmith, Commented Sep 1, 2020 at 18:57
@thatjeffsmith - Yes, you are right. I am good with either rows or columns as i can manipulate later to the format i want. I am not able to read the contents only. I will follow your steps and read the blogs to see if i can do it? — Sushant
– Sushant, Commented Sep 1, 2020 at 19:24

thatjeffsmith · Accepted Answer · 2020-09-01 18:48:24Z

2

Step One - add an IS JSON check constraint to your BLOB_CONTENT column.

ALTER TABLE CLOBS
ADD CONSTRAINT CLOB_JSON CHECK 
(CLOBS IS JSON)
ENABLE; -- yes my table name and my column are both named CLOBS

Step Two - Add some data.

The database provides native SQL calls to parse/query JSON content in your BLOB.

My data, a single row. This JSON document has a couple of simple arrays.

{
  "results" : [
    {
      "columns" : [
        {
          "name" : "REGION_ID",
          "type" : "NUMBER"
        },
        {
          "name" : "REGION_NAME",
          "type" : "VARCHAR2"
        }
      ],
      "items" : [
        {
          "region_id" : 1,
          "region_name" : "Europe"
        },
        {
          "region_id" : 2,
          "region_name" : "Americas"
        },
        {
          "region_id" : 3,
          "region_name" : "Asia"
        },
        {
          "region_id" : 4,
          "region_name" : "Middle East and Africa"
        }
      ]
    }
  ]
}

I can use the jsonv_value() function if I want to pull a single attribute out, and I can reference those using $. notation. I reference arrays as you'd expect.

select json_value(CLOBS,'$.results.columns[0].name')   FIRST_COLUMN,
       json_value(CLOBS,'$.results.columns[1].name')   SECOND_COLUMN
  from CLOBS
 where ID = 1;

The results -

Our product architect (Beda) has a great blog series with much better examples than this.

edited Sep 1, 2020 at 18:48

answered Sep 1, 2020 at 18:33

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sushant Over a year ago

Thank you. I have updated the question with an example of my json file

thatjeffsmith Over a year ago

i don't have time to circle back right now, someone else probably will, but go read those posts from Beda, he has examples for exactly what you want.

Collectives™ on Stack Overflow

Reading JSON data from BLOB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related