FOR JSON Clause in a SQL SELECT statement

You can execute queries that return scanned data and/or fields from the NCS header as JSON.

The SELECT SQL statement now supports the FOR JSON clause. The supported syntax is:

SELECT <columns> FROM <table> [<with NCSHEADER >][ <where-clause>] FOR JSON

Upon execution the query returns the data along with the column names. This result is a standard JavaScript array serialized to a string in JSON. There are as many elements in the array as rows returned by the query. The array is empty if the query does not return any rows. Each array element is a map of key-value pairs, no ordering is implied. The key is the column (or alias) specified in the SQL statement. The value is the column value formatted as a string. Null values are appropriately represented – see the examples that follow.

Example:

select * from [GPAS #221666 Dual Sided] for json

Returns:

[{"Birthdate":"1978-02-28","Clips":null,"Gender":"F","Grade":"00","Ident":"4545067460","Items":"123451234512 1234512345 123 12345 12345 12 1234512345 3 2 5","Name":"QUARTER RIGHT ","ROWID":"000003","Score":" ","Spec Codes":"345567"}]

Interpretation:

The query returned one row. Notice that the Clips column is null because image is not supported.

Example:

select Name, Birthdate as BirthDate, Gender from [GPAS #221666 Dual Sided] for json

Returns:

[{"BirthDate":"1978-02-28","Gender":"F","Name":"QUARTER RIGHT "}]

Interpretation:

Notice that an alias was specified to correct the capitalization of Birthdate. The map entries are enumerated by the column name, but that is simply an implementation detail that could well change in a future version. Do not rely on such an ordering. You should not parse this query output on your own: use a deserializer in the language of your choice.

Example:

select * from [GPAS #221681 Tutorial] for json

Returns:

[]

Interpretation:

The query did not return any rows, resulting in an empty array.

Example:

select [Serial Number], Name, Gender from [GPAS #221666 Dual Sided] with (NCSH) where [Serial Number] < 5 for json

Returns:

[{"Gender":"F","Name":"ALL RIGHT ","Serial Number":"1"},{"Gender":"M","Name":"HALF RIGHT ","Serial Number":"2"},{"Gender":"F","Name":"QUARTER RIGHT ","Serial Number":"3"},{"Gender":"-","Name":"ALL WRONG ","Serial Number":"4"}]

Interpretation:

The query returned 4 rows. Notice that we included the serial number column from the NCS header. We also limited the query result by specifying a where clause.

Example:

select * from [Edit Failures] where ([Serial Number] = 2) AND ([Sheet Number] <= 1) for json

Returns:

[{"Column Ordinal":"1","Document Name":"GPAS #221666 Dual Sided","Edit Ordinal":"1","EditFailureID":"000002000001","FailureMode":"1","Field Name":"Name","Message":"Area should not contain multiples. Area should not be blank. ","Multiples":"-1","Omits":"-1","Serial Number":"2","Sheet Number":"1","Value":"**L RIGHT "},{"Column Ordinal":"4","Document Name":"GPAS #221666 Dual Sided","Edit Ordinal":"4","EditFailureID":"000002000004","FailureMode":"128","Field Name":"Birthdate","Message":"Area should be complete and contain a valid date. Area should not be blank. ","Multiples":"-1","Omits":"-1","Serial Number":"2","Sheet Number":"1","Value":"**2*1947"},{"Column Ordinal":"7","Document Name":"GPAS #221666 Dual Sided","Edit Ordinal":"7","EditFailureID":"000002000007","FailureMode":"64","Field Name":"Items","Message":"Area should contain no more than 0 omits (0) and 0 multiples (1). Area should not be blank. Area should be between 1 and 5. ","Multiples":"1","Omits":"0","Serial Number":"2","Sheet Number":"1","Value":"*234512345123451234512345123451234512345123451234512345123451234512345123451234512345123451234512345123451234512345123451234512351234512345123451234512345123451234512345123451234512345123451234512345"}]

Interpretation:

This is a query that examines a single scanned data record (serial number 2) for edit failures. Notice that multiple rows were returned. Each row describes an edit failure.