Oracle SQL Developer Web: Importing JSON as Rows in a Relational Table

You have:

And as a ‘database person’ – what you want:

SQL Developer Web & the Import Feature

In a SQL Developer Web (version 20.2) Worksheet – look down ‘here’

So in version 20.2, I can now browse XML, JSON, and AVRO files…and then import their contents to new tables!

If you’re on Oracle Autonomous, you’re very close to having an upgrade and this feature becoming available – for now this is available for when you deploy your own ORDS and enable SQL Developer Web.

Let’s try this with a simple JSON file.

Clicking the ‘Cloud Upload’ looking button, and using Finder/Explorer to select my file…I’ll get a screen that looks like this:

If we proceed to the end, we have our new table!

And all my rows are now available with basic SQL 101

But Jeff, my {json} ain’t so simple!

Let’s look at a slightly more complicated json document. Something with complex attributes.

[{
		"name": "Jeff",
		"age": 40,
		"cars": {
			"car1": "Honda",
			"car2": "BMW",
			"car3": "Ford"
		}
	},
	{
		"name": "Kris",
		"age": 50,
		"cars": {
			"car1": "Porsche",
			"car2": "Mini",
			"car3": "Jeep"
		}
	}
]

If we go to import THIS, we’ll have 3 columns as expected…

And onto the column definitions –

And with my CARS table created, I can now query it.

And since the cars column has the ‘IS JSON’ constraint, I can do this:

But Jeff, I already have a table, I just want to import my data!

No worries! Just right-click on your existing table in the SQL Worksheet navigator.

XML & AVRO

AVRO is a specialized form of JSON. It contains a data dictionary describing the JSON document AND it’s compressed. So it’s easier to define your new table, and you can put a TON of data into a single file.

XML – well, you know what XML is. When you get come across a nested element, we’ll put that in as a text field with the “IS_JSON” constraint as well – not as yet another XML column.