FOR JSON clause
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
While both XML and JSON usage in SQL has been standardised in more recent versions of the SQL standard, SQL Server has always had some very convenient utilities at the end of a SELECT statement, which allow for converting SQL tables into the most common XML or JSON representations.
Starting with jOOQ 3.14, these syntaxes are supported in jOOQ as well, and if possible, emulated in other dialects which have native XML or JSON support.
JSON is just XML with less syntax and less features. So the FOR JSON syntax in SQL Server is almost the same as the above FOR XML syntax from the previous section:
SELECT id, title FROM book ORDER BY id FOR JSON PATH
create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.ID)
.forJSON().path()
.fetch();
This query produces a document like this:
[
{"id": 1, "title": "1984"},
{"id": 2, "title": "Animal Farm"},
{"id": 3, "title": "O Alquimista"},
{"id": 4, "title": "Brida"}
]
Table of contents
- 3.5.3.14.1.
- AUTO mode
- 3.5.3.14.2.
- PATH mode
- 3.5.3.14.3.
- ROOT directive
- 3.5.3.14.4.
- INCLUDE_NULL_VALUES directive
- 3.5.3.14.5.
- WITHOUT_ARRAY_WRAPPER directive
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!