You have:

A {json} array []

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

It’s SQL – I know this!

SQL Developer Web & the Import Feature

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

This import feature new. What IS new are the highlighted file types.

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:

Nothing new here…

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

All rows were successfully imported.

And all my rows are now available with basic SQL 101

Ta-da!

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…

Completely fake data.

And onto the column definitions –

Our ‘complex’ column remains as JSON, and the table wizard conveniently adds the JSON constraint for you.
Adding this constraint gives you a lot of features in 12c+ when it comes to querying your JSON

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

Ta-da, part 2.

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

The most very basic of JSON features in Oracle SQL, but you get the idea I hope.

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.

Pick the file, follow the prompts. Be sure to mach up the columns so the data goes in correctly.

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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

2 Comments

    • thatjeffsmith

      That’s completely different than what I’m talking about.

      Oracle also has core json support built into the database.

Write A Comment