I have this sample query:

SELECT
    1       AS tag,
    null    AS parent,
    row_num AS [enc_obj!1!row_num]
FROM
    sa_rowgenerator(1, 2)
ORDER BY
    3
FOR JSON EXPLICIT;

I get this as a result:

[
    {
        "enc_obj": [
            {
                "row_num": 1
            }
        ]
    },
    {
        "enc_obj": [
            {
                "row_num": 2
            }
        ]
    }
]

How do I modify the query to get this:

{
    "enc_obj": [
        {
            "row_num": 1
        },
        {
            "row_num": 2
        }
    ]
}

asked 30 Jan, 09:44

Valdas's gravatar image

Valdas
287121524
accept rate: 80%


This example will do nearly what you want. It just adds a null value. Maybe somebody can help out on that.

SELECT
    1       AS tag,
    null    AS parent,
    null    AS [enc_obj!1!row_num]
 FROM
    sa_rowgenerator(1, 2)
UNION 
select
    1
   ,1
   ,row_num
 FROM
    sa_rowgenerator(1, 2)
ORDER BY
    3, 1
FOR JSON EXPLICIT;

The result is:

[{
    "enc_obj": [{
        "row_num": null
    }, {
        "row_num": 1
    }, {
        "row_num": 2
    }]
}]

permanent link

answered 02 Feb, 14:36

Frank's gravatar image

Frank
583142136
accept rate: 17%

not sure if this might help. did a bit of string manipulation tough

create or replace function tt()

returns long varchar

begin

declare     ls_json     long varchar;
select
    (
    SELECT
        1       AS tag,
        null    AS parent,
        null    as [!1!],
        null AS [enc_obj!2!row_num]
    FROM
        sa_rowgenerator(1, 2)

    UNION all

    SELECT
        2,
        1,
        null,
        row_num
    FROM
        sa_rowgenerator(1, 2)
    for json explicit
)
into ls_json;

set ls_json  = substr(ls_json, locate(ls_json,'null,') + 5);
set ls_json = left(ls_json,len(ls_json) - 2 );

return ls_json;

end

select tt()

permanent link

answered 03 Feb, 11:34

Dev's gravatar image

Dev
81114
accept rate: 20%

edited 03 Feb, 11:36

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19

question asked: 30 Jan, 09:44

question was seen: 327 times

last updated: 03 Feb, 11:36