If I do this I can get the first value in the array. But how can I get all of the values in the list ?
SELECT
JSON_VALUE('{"Distributor": [5030, 4000, 1231]}', '$.Distributor[0]') AS result;
-
1You should be able to use OPENJSON but I haven't cracked the format yetNick.Mc– Nick.Mc2019-01-07 23:54:27 +00:00Commented Jan 7, 2019 at 23:54
-
How do you want to display all of the entries in the array? This is an important consideration.Laughing Vergil– Laughing Vergil2019-01-07 23:59:29 +00:00Commented Jan 7, 2019 at 23:59
-
You can use openjson cobinated with outer applySanpas– Sanpas2019-01-08 00:06:22 +00:00Commented Jan 8, 2019 at 0:06
-
1Possible duplicate of SQL Server OPENJSON read nested jsonNick.Mc– Nick.Mc2019-01-08 00:19:03 +00:00Commented Jan 8, 2019 at 0:19
Add a comment
|
2 Answers
SELECT value
FROM OPENJSON('{"Distributor": [5030, 4000, 1231]}','$.Distributor')
'$.Distributor' is the 'start path' to start looking for an array
Comments
You can take look at this article: https://learn.microsoft.com/fr-fr/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017
Using function Openjson and Outer apply
Example provided:
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },
{ "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"] }, "dob": "2005-11-04T12:00:00" }
]'
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',
age int, dateOfBirth datetime2 '$.dob',
skills nvarchar(max) '$.info.skills' as json)
outer apply openjson( skills )
with ( skill nvarchar(8) '$' )
EDIT Code with provided json :
DECLARE @json NVARCHAR(MAX)
SET @json = N'{"Distributor": [5030, 4000, 1231]}'
SELECT Distributor
FROM OPENJSON(@json)
WITH (Distributors nvarchar(max) '$.Distributor' as json)
outer apply openjson( Distributors )
with ( Distributor int '$' )
RESULT :
Distributor
5030
4000
1231
Comments
