Oracle SQLcl: Spool to json, only include content in items array?

Question

I'm making a query via Oracle SQLcl. I am spooling into a .json file.

The correct data is presented from the query, but the format is strange.

Starting off as:

SET ENCODING UTF-8 SET SQLFORMAT JSON SPOOL content.json

Follwed by a query, produces a JSON file as requested.

However, how do I remove the outer structure, meaning this part:

{"results":[{"columns":[{"name":"ID","type":"NUMBER"},
{"name":"LANGUAGE","type":"VARCHAR2"},{"name":"LOCATION","type":"VARCHAR2"},{"name":"NAME","type":"VARCHAR2"}],"items": [
    // Here is the actual data I want to see in the file exclusively
  ]

I only want to spool everything in the items array, not including that key itself.

Is this possible to set as a parameter before querying? Reading the Oracle docs have not yielded any answers, hence asking here.

Answer 1

Thats how I handle this.

After output to some file, I use jq command to recreate the file with only the items

ssh cat file.json | jq --compact-output --raw-output '.results[0].items' > items.json `

Using this library = https://stedolan.github.io/jq/