Create nested JSON arrays using FOR JSON PATH

by

You can use the following query:

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
    (
        SELECT pt.pet_id AS id, pt.pet_name AS name 
        FROM @Pets pt WHERE pt.pet_owner=pr.person_id 
        FOR JSON PATH
    ) AS [person.pet]
FROM @Persons pr 
FOR JSON PATH, ROOT('pet owners')

For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

More Related Contents:

  1. How to check if a column exists in a SQL Server table
  2. Parse JSON in TSQL
  3. How do I move a table into a schema in T-SQL
  4. How do I escape a single quote in SQL Server?
  5. Only inserting a row if it’s not already there
  6. Unpivot with column name
  7. How do I initialize a TypeScript Object with a JSON-Object?
  8. Selecting data from two different servers in SQL Server
  9. Create HTML Table with SQL FOR XML
  10. Posting a File and Associated Data to a RESTful WebService preferably as JSON
  11. How do I get list of all tables in a database using TSQL?
  12. How to escape double quotes in JSON
  13. How to convert arbitrary simple JSON to CSV using jq?
  14. Escape a string in SQL Server so that it is safe to use in LIKE expression
  15. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  16. With JSONDecoder in Swift 4, can missing keys use a default value instead of having to be optional properties?
  17. Using curl POST with variables defined in bash script functions
  18. How can I customize serialization of a list of JAXB objects to JSON?
  19. T-SQL Dynamic SQL and Temp Tables
  20. Can’t send a post request when the ‘Content-Type’ is set to ‘application/json’
  21. Angular: ‘Cannot find a differ supporting object ‘[object Object]’ of type ‘object’. NgFor only supports binding to Iterables such as Arrays’
  22. In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
  23. Best equivalent for IsInteger in SQL Server
  24. OpenAPI query string parameter with list of objects
  25. How to convert postgres json to integer
  26. OpenAPI path/query parameters nested structure serialization
  27. Include missing months in Group By query
  28. Can anyone explain how to get all the mismatch between two responses in karate?
  29. COALESCE Function in TSQL
  30. CORS enabled but response for preflight has invalid HTTP status code 404 when POSTing JSON

Leave a Comment