JSON Without Array Wrapper on Lower Levels

Create nested JSON arrays using FOR JSON PATH

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/

SQL - How to store JSON_QUERY output to variable

Following VBokšić's link I made this query as answer:

DECLARE @json NVARCHAR(MAX)

SET @json = (SELECT (JSON_QUERY((
    SELECT 'Ball' AS title, 
           'Blue' AS color, (
                SELECT '1' AS Opt1, 
                       '2' AS Opt2 
                FOR JSON PATH
           ) AS Options
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ))) AS product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

SELECT @json

Output:

{"product":{"title":"Ball","color":"Blue","Options":[{"Opt1":"1","Opt2":"2"}]}}

Return results in custom JSON format

You may try with the following statement, using FOR JSON PATH and dot-separated column names:

Statement:

SELECT
   '12345' AS 'values.param1', 
   'abcdef' AS 'values.param2'  
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{"values":{"param1":"12345","param2":"abcdef"}}

How do I deserialize a JSON array without needing a wrapper type?

Just deserialize the vector directly:

let workspaces = serde_json::from_str::<Vec<Workspace>>(input);

How do you OPENJSON on Arrays of Arrays

While you can't get away with using only a single OPENJSON, you can simplify your query a bit to make it easier to create dynamically by removing the nested subqueries:

SELECT SectionName, SectionOrder, RenderName, RenderOrder, FieldName, FieldData
FROM OPENJSON(@layout, '$.Sections') 
WITH (
    SectionName NVARCHAR(MAX) '$.SectionName',  
    SectionOrder NVARCHAR(MAX) '$.SectionOrder', 
    Renders NVARCHAR(MAX) '$.Renders' AS JSON
)
CROSS APPLY OPENJSON(Renders,'$')
WITH (
    RenderName NVARCHAR(MAX) '$.RenderName',  
    RenderOrder NVARCHAR(MAX) '$.RenderOrder', 
    Fields NVARCHAR(MAX) '$.Fields' AS JSON
)
CROSS APPLY OPENJSON(Fields,'$')
WITH (
    FieldName NVARCHAR(MAX) '$.FieldName',  
    FieldData NVARCHAR(MAX) '$.FieldData'
)

EDIT:
If you have a primitive array, you can access the data using the value property after you expose the nested array as a JSON field. Using the JSON from the comment below, you can do this to get the values from a primitive array:

DECLARE @layout NVARCHAR(MAX) = N'{ "id":123, "locales":["en", "no", "se"] }'

SELECT 
    a.id
    , [Locale] = b.value 
FROM OPENJSON(@layout, '$') 
WITH (
    id INT '$.id',  
    locales NVARCHAR(MAX) '$.locales' AS JSON
) a
CROSS APPLY OPENJSON(a.locales,'$') b

Select last value from Json array

You can try this

DECLARE @json NVARCHAR(MAX) =   
N'{ 
    "solution": "xxxxxxxxxxxxxxxxxxxxx",
    "options": [
        {
            "choice_id": 205073,
            "choice": "aaaa"
        },
        {
            "choice_id": 205074,
            "choice": "bbbb"
        },
        {
            "choice_id": 205075,
            "choice": "cccc"
        },
        {
            "choice_id": 205076,
            "choice": "dddd"
        }
    ]
}';

SELECT TOP 1 
         A.solution
        ,JSON_QUERY(B.[value]) AS options
FROM OPENJSON(@json) WITH (solution nvarchar(max), options NVARCHAR(MAX) AS JSON) A
CROSS APPLY OPENJSON(A.options) B
ORDER BY B.[key] DESC
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

The result

{"solution":"xxxxxxxxxxxxxxxxxxxxx"
,"options":{
            "choice_id": 205076,
            "choice": "dddd"
           }
}

Some explanation

FROM OPENJSON will dive into your JSON and find the top-level elements. We fetch solution as is and we state AS JSON for options to specify, that this value will be treated as JSON later on.

The CROSS APPLY will call OPENJSON again, but this time against the options and we will get an array back. The column key is the ordinal position within the array, so we can use TOP 1 and ORDER BY key DESC to get the last element in the array.

Before we can re-assemble this query to a JSON we have to wrap B.value with JSON_QUERY(). Otherwise we would see escaped characters like \t or \r\n within the JSON. The reason: Without this, the JSON-string would not be taken as a JSON-string but as any other string and would be packed into the result as one single text value.

---

Related Topics