SQL Anywhere 16.0.0.1915 json result

[fvestjens]

If I execute the following query in I-SQL

SELECT top 2 Identifier id, Description Text

FROM Tax

order by id for json raw

I get the following result:

[{"id":1,"Text":"AEU Domestic delivery GB"},{"id":2,"Text":"AEU Domestic delivery BE"}]

That's what I expect.

However if I use this query as a subquery in another query:

SELECT

(SELECT top 2 Identifier id, Description Text

FROM Tax

order by id for json raw) as result,

(if (select count() from Tax) > 2 then 'True' else 'False' endif) as more

for json raw

I get the following result:

[{"result":"[{\"id\":1,\"Text\":\"AEU Domestic delivery GB\"},{\"id\":2,\"Text\":\"AEU Domestic delivery BE\"}]","more":"True"}]

How do I get rid of these '\'. I wasn't expecting them.

Regards,

Frank

[lucjan_chmura · Accepted solution]

Hi ,

I believe this is correct behavior.

Since your subquery returns JSON, it contains double quotes. Because you use JSON again on these results, these double quotes need to be escaped with backslash.

This would be similar if you use JSON on data which already contains double quotes e.g:

select '"""' as double_quotes for json raw;

returns :

[{"double_quotes":"\"\"\""}]

I'm not sure why would you like to remove this , but if you really need to than you could use REPLACE function :

[Former Member]

To elaborate on Lucjan's suggestion, you can create a table to store JSON object and insert into that table with your select statement. Once you have inserted it, you can select on the table and use replace function to get rid of '\' character.

Here is an example :

1)

CREATE TABLE JSONobj(

     jsonObject long varchar

)

2)

INSERT INTO JSONobj SELECT

(SELECT top 2 Identifier id, Description Text From Tax

order by id for json raw) as result,

(if (select count() from Tax ) > 2 then 'True' else 'False' endif) as more

for json raw;

3)

SELECT replace(JSONobj.jsonobject, '\', '') from JSONobj;

I know it requires more work, but this is one of a workaround.

Thanks.

[former_member207653]

Same result with SQL Anywhere 16.0.0.2158 and 17.0.0.1211, just tested.