My problem is that I have a JSON column with a key which contains an array of string values. If I access it with array notation:
-- JSON Key = "infoProvided":["FullName","Contact Information"]
select top 1 json_value(textarea,'$.infoProvided[0]') from checklist
-- result
(No column name)
FullName
-- expected
(No column name)
FullName,Contact Information
I need to get all possible values in the array. Thanks in advance.
Note: I had to edit your JSON as it was missing the open and closing curly braces.
DECLARE @json VARCHAR(1000) = '{"infoProvided":["FullName","Contact Information"]}';
SELECT * FROM OPENJSON ( @json, '$.infoProvided' );
Returns
+-----+---------------------+------+
| key | value | type |
+-----+---------------------+------+
| 0 | FullName | 1 |
| 1 | Contact Information | 1 |
+-----+---------------------+------+
SELECT STRING_AGG ( [value], ',' ) AS InfoList FROM OPENJSON ( @json, '$.infoProvided' );
Returns
+------------------------------+
| InfoList |
+------------------------------+
| FullName,Contact Information |
+------------------------------+
SELECT * FROM OPENJSON ( @json, '$' );
Returns
+--------------+------------------------------------+------+
| key | value | type |
+--------------+------------------------------------+------+
| infoProvided | ["FullName","Contact Information"] | 4 |
+--------------+------------------------------------+------+
SELECT JSON_QUERY ( @json, '$.infoProvided' );
Returns
+------------------------------------+
| (No column name) |
+------------------------------------+
| ["FullName","Contact Information"] |
+------------------------------------+
