Anyway, using string methods might lead into troubles, when it comes to characters which need escaping (e.g. nvarchar(max) '$.values' AS JSONĬROSS APPLY OPENJSON(j1. 1 No, this approach with correlated sub-queries is the correct way to solve nested JSON issues (same with nested XML). I am trying to write a query similar to the one below, that returns the ID column and all of the WorkstationID values. I have access to ISJSON, JSONVALUE and JSONQUERY, but not OPENJSON. or add all columnsĬROSS APPLY OPENJSON(t.JsonColumn, '$.values') WITH ( In a SQL Server database, I have a table that contains 2 columns, ID and JSON.The JSON column contains JSON-formatted text. The JSON column contains JSON-formatted text. Note, that you need to use AS JSON in a "values" column definition to specify that the referenced property contains an inner JSON array and the type of that column must be nvarchar(max). SELECT top 1 labelType AS documentType, ( select producingPlant, productionOrder from test T2 with (Nolock) where T1.ordinalid t2.ordinalid FOR JSON Path ) AS dbsKeyData, ( select Method, PrinterId, SharedLocation, Path, FileName, Overwrite, Watermark, Content from ( select ordinalid, 'Print' as Method,Printer as PrinterId . How to extract list of values from JSON in SQL Server Ask Question Asked 2 years, 8 months ago Modified 2 years, 8 months ago Viewed 2k times 0 In a SQL Server database, I have a table that contains 2 columns, ID and JSON. The statement depends on the structure of the parsed JSON, in your case you need to use two nested OPENJSON() calls and additinal APPLY operators.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |