Forum Stats

  • 3,757,167 Users
  • 2,251,204 Discussions
  • 7,869,748 Comments

Discussions

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

3272816
3272816 Member Posts: 3
edited Nov 9, 2018 11:11AM 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,782 Bronze Crown
    edited Nov 9, 2018 2:48AM 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 Nov 8, 2018 4:05PM

    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,782 Bronze Crown
    edited Nov 9, 2018 2:48AM 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 Nov 9, 2018 11:09AM

    perfect.  Thank you very much for the workaround.

  • 3272816
    3272816 Member Posts: 3
    edited Nov 9, 2018 11:10AM

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