How to concatenate multiple FOR JSON AUTO selects into a single varchar?

[Former Member]

SQL Anywhere 16.0.0.1915

We are developing a JSON payload to be passed via a web service to an IOS application. It is simple enough to use the FOR JSON AUTO clause and I have obtained a variety of JSON selects. But our iPad developers have asked for a combined payload that would concatenate several selects into a single long varchar.

How to accomplish that escapes me.

[VolkerBarth]

into a single long varchar.

As JSON uses UTF-8 encoding by default, you might prefer a long nvarchar.

[Former Member · Accepted solution]

Thanks for the ideas, looks like I will just "string" the data together. (Glad JSON is rather simple in that regard.)

The basic SELECT... FOR JSON is so easy, I was hoping for one more silver bullet: - SELECT * FROM A FOR JSON INTO ls_jsona; - SELECT * FROM B FOR JSON INTO ls_jsonb; - SELECT ls_jsona || ls_jsonb; OR: - SELECT * FROM A FOR JSON UNION ALL SELECT * FROM B FOR JSON;

Guess I'll have to actually do some work :-).

Thanks, Bill

[thomas_duemesnil]

If you have create for example two JSON Strings in two variables.

set json1 = '{"Name": "Bob"};
set json1 = '{"Name": "Mary"};

You can combine these two JSON Objects in an array.

select string('[', json1, ',', json2, ']';

So instead of having different web service calls for different select statements you concatenate them in one variable and return the combine result as payload.

HTH

[VolkerBarth]

Likewise you could create a JSON object instead of an array:

select string('{ "obj1" : ', json1, ', "obj2" : ', json2, '}';