How do you retrieve JSON formatted data from SQL Anywhere 17?

SQL Anywhere 17 is a SAP Database.  Some of the earlier versions were released by SYBASE.

In SQL Anywhere 17 you have three different ways of getting JSON formatted data / JSON document.

You can use the FOR JSON clause  with:

• SELECT Statement
• Subqueries
• Queries having Group By clause
• Aggregate Functions and
• Views

The result is a JSON array consisting of:

• Scalar elements
• Objects
• Arrays

There are three ways of calling the FOR JSON Clause:

• For JSON Raw
• For JSON AUTO
• For JSON Explicit

Note that SQL Server 2016 did get JSON support for the first time and has only For JSON Auto clause.

You run SQL Queries in Interactive SQL. Here is an example of a query that provides json document using the FOR JSON AUTO clause.

InteractiveSQL17JSON

Can you return data in JSON format from a web service in SQL Server?

The short answer is yes provided we run our queries in SQL Server 2016.

We have seen in an earlier post using ODATA service to generate a report from Power BI.

These were some example OData services that were considered in the previous mentioned link.

Northwind traders here:
http://services.odata.org/northwind/northwind.svc
http://services.odata.org/V4/Northwind/Northwind.svc

Adventure Works data here:
http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

SQL Server 2016 supports JSON and it is possible to run a query in SQL Server Management Studio to return data from a ODATA service by running a query fashioned a shown.
=======
SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',  
ProductID, Name as ProductName  
FROM Production.Product 
WHERE ProductID<400 br="">FOR JSON AUTO 
=======
This retrieves the following result:

Note that the size of text returned is limited by the settings.