Forum Stats

  • 3,733,354 Users
  • 2,246,747 Discussions
  • 7,856,665 Comments

Discussions

Is it possible to retrieve content only with 'sqlformat json output', i.e. not include metadata?

3272816
3272816 Member Posts: 3
edited November 2018 in SQLcl

Are there any settings, or combination of settings, in sqlci that would eliminate metadata {"results"} - providing content only?

test case

drop table example;

create table example (pk numeric, description varchar2(50));

insert into example values (1, 'First');

insert into example values (2, 'Second');

commit;

set sqlformat json

select * from example;

What sqlci returns:

{"results":[{"columns":[{"name":"PK","type":"NUMBER"},{"name":"DESCRIPTION","type":"VARCHAR2"}],"items":

[

{"pk":1,"description":"First"}

,{"pk":2,"description":"Second"}

]}]}

Desired output:

{"pk":1,"description":"First"}

,{"pk":2,"description":"Second"}

Regards

3272816

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited November 2018 Accepted Answer

    Here's a workaround...

    On Linux, using sed, for example:

    $ cat json.sqlset sqlformat jsonset feedback offwith example (pk, description) as (   select 1, 'Level '||level from dual connect by level <10)select pk,       descriptionfrom   example/exit;$ ./sql -s gaz/[email protected] @json.sql | sed -e '1,2d;$d' -e '$d'{"pk":"1","description":"Level 1"},{"pk":"1","description":"Level 2"},{"pk":"1","description":"Level 3"},{"pk":"1","description":"Level 4"},{"pk":"1","description":"Level 5"},{"pk":"1","description":"Level 6"},{"pk":"1","description":"Level 7"},{"pk":"1","description":"Level 8"},{"pk":"1","description":"Level 9"}$

    Here's a windows cmd command-line option, using the same example json.sql, making use of "more +2" and "find -v" to suppress 1st 2 lines and last line:

    F:\Oracle>sql -s gaz/[email protected] @json.sql | more +2 | find /v "]}]}"{"pk":1,"description":"Level 1"},{"pk":1,"description":"Level 2"},{"pk":1,"description":"Level 3"},{"pk":1,"description":"Level 4"},{"pk":1,"description":"Level 5"},{"pk":1,"description":"Level 6"},{"pk":1,"description":"Level 7"},{"pk":1,"description":"Level 8"},{"pk":1,"description":"Level 9"}F:\Oracle>
    32728163272816

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited November 2018

    It appears the answer is no.  Logically, I thought "sqlformat json" might respect the standard "set heading off" to suppress that metadata portion of the output, but that still does not work as of SQLcl 18.3.

    Someone suggested it long ago (SQLcl - customize sqlformat json? ) but no action yet.  You might try adding a Feature Request in the SQL Developer Exchange (http://sqldeveloper.oracle.com).

    Cheers

    32728163272816
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited November 2018 Accepted Answer

    Here's a workaround...

    On Linux, using sed, for example:

    $ cat json.sqlset sqlformat jsonset feedback offwith example (pk, description) as (   select 1, 'Level '||level from dual connect by level <10)select pk,       descriptionfrom   example/exit;$ ./sql -s gaz/[email protected] @json.sql | sed -e '1,2d;$d' -e '$d'{"pk":"1","description":"Level 1"},{"pk":"1","description":"Level 2"},{"pk":"1","description":"Level 3"},{"pk":"1","description":"Level 4"},{"pk":"1","description":"Level 5"},{"pk":"1","description":"Level 6"},{"pk":"1","description":"Level 7"},{"pk":"1","description":"Level 8"},{"pk":"1","description":"Level 9"}$

    Here's a windows cmd command-line option, using the same example json.sql, making use of "more +2" and "find -v" to suppress 1st 2 lines and last line:

    F:\Oracle>sql -s gaz/[email protected] @json.sql | more +2 | find /v "]}]}"{"pk":1,"description":"Level 1"},{"pk":1,"description":"Level 2"},{"pk":1,"description":"Level 3"},{"pk":1,"description":"Level 4"},{"pk":1,"description":"Level 5"},{"pk":1,"description":"Level 6"},{"pk":1,"description":"Level 7"},{"pk":1,"description":"Level 8"},{"pk":1,"description":"Level 9"}F:\Oracle>
    32728163272816
  • 3272816
    3272816 Member Posts: 3
    edited November 2018

    perfect.  Thank you very much for the workaround.

  • 3272816
    3272816 Member Posts: 3
    edited November 2018

    I had not seen that earlier question, and good suggestion.  Thanks Glen.

Sign In or Register to comment.