Sunday, November 27, 2016

Retrieve data from SQL Anywhere 17 in JSON format

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