CODEWITHSUNDEEP
arraysjsonsql-server
macrolyte
macrolyte

Reputation: 352

Accessing JSON Array of strings

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.

Upvotes: 0

Views: 164

Answers (1)

critical_error
critical_error

Reputation: 6716

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"] |
+------------------------------------+

Upvotes: 1

Related Questions