0

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.

cbll
  • 6,499
  • 26
  • 74
  • 117
  • 1
    There isn't a way to control that. – Kris Rice Aug 14 '18 at 11:33
  • Why aren't you just querying the items, or extracting the item array from the whole document as part of the query? A client parameter isn't going to modify the results of the query like this. – Alex Poole Aug 14 '18 at 17:58
  • That is what I would like to do, @AlexPoole - how would I query just the items? Not sure if I get you 100% though – cbll Aug 15 '18 at 08:47
  • If you edit your question to include your table structures, some sample data and your current query we should be able to tell you how to modify it. Also please include your Oracle version, and the expected result you want for your sample data. – Alex Poole Aug 15 '18 at 08:51

1 Answers1

0

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/

calebeaires
  • 1,972
  • 1
  • 22
  • 34