I want to enclose the sql query out with open and close brackets [ 1,2,3 ] — oracle-tech

    Forum Stats

  • 3,714,821 Users
  • 2,242,634 Discussions
  • 7,845,081 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

I want to enclose the sql query out with open and close brackets [ 1,2,3 ]

User_N2CZ9
User_N2CZ9 Member Posts: 4 Green Ribbon

Hi,


I'm trying to generate a SQL query output with open and close bracket need to provide this output for a req.


Now I'm using the following query to achieve this.


select '[' from dual

union all

select account_numbr from table

union all

select ']' from dual


out put:

[1,2,34,56]


Do you have any function to achieve this to get the out put with open and close brackets?


I appreciate your inputs on this.


Thanks,

Kumar

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond

    Hi, @User_N2CZ9

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: How to Ask Questions in Developer Community Spaces - oracle-tech


    What's wrong with the query you're using now?

  • mathguy
    mathguy Member Posts: 9,463 Gold Crown
    edited January 27

    The "usual way" would be something like this (note that || is the concatenation operator in Oracle):

    select '[' || listagg(account_number, ',') within group (order by account_number) || ']' as output
    from   table.....
    


    However, your output looks suspiciously like a JSON array, in which case you might want to use JSON-specific tools. They are only supported since Oracle 12.1 - this is why it's so important to know your version. (Moreover, the function I use below may have been added only in Oracle 12.2 - you will have to experiment yourself to find out.)

    The output is the same, but the intent is clearer. It would look like this:


    select json_arrayagg(account_number order by account_number) as output
    from   table.....
    


  • Gaz in Oz
    Gaz in Oz Member Posts: 3,754 Bronze Crown
    edited January 27

    here's an actual example of json_arrayagg usage, (available from Oracle 12.2 onward), complete with documentation on how to use it:

    You should refer to the documentation that matches the database version you are using.

  • User_N2CZ9
    User_N2CZ9 Member Posts: 4 Green Ribbon

    Yes, Its kind of Jason out put the file to be passed to the restful service.


    I'm expecting one single open bracket and close bracket to the complete SQL ouput.


    ex:


    select column1,column2 from table.


    The output should be like


    [ column1,column2]


    with this

    select '[' || listagg(account_number, ',') within group (order by account_number) || ']' as output
    from   table.....
    


    with this select query


    it is adding [ and ] to each row.


    [column1],[column2]


    I want the out put like


    [column1,column2].


    Please let me know if you need any additional information,


    Thanks,

    kumar

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,951 Red Diamond
    edited February 1

    Hi,

    I want the out put like


    [column1,column2].

    That's exactly wat the query you posted produces. Since I don't have a test copy of your table, I tried it on the scott.dept table, like this:

    select '[' || listagg (deptno, ',') within group (order by deptno) || ']' as output
    from  scott_dept;
    

    That query produces these results:

    OUTPUT

    ------------------------------

    [10,20,30,40]

    Once again, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that data.

  • mathguy
    mathguy Member Posts: 9,463 Gold Crown

    In your original message, your attempt included the following (in addition to the brackets):

    select account_number from table
    

    and the desired output looked consistent with that - it seemed to be a list of account numbers.

    Now you mention two columns. Where is that coming from? What can that possibly have to do with the way you asked the question originally?

    Good luck with your project.

Sign In or Register to comment.