Your statement is correct and based on the JSON data in the question, the reason for this "... JSON text is not properly formatted. Unexpected character 'h' is found at position 0' error ... " error is somewhere else.
Your JSON text is a valid JSON array, so you have two possible approaches to get your expected results:
- if this
JSON array has always two items, you should use JSON_VALUE() to access each item
- if the count of the items is not known, you should use
OPENJSON() with additional CROSS APPLY operator and string aggregation function.
Table:
CREATE TABLE #Data (
[gca] nvarchar(max)
)
INSERT INTO #Data
([gca])
VALUES
(N'[{"i":"https://some.image.URL 1","u":"https://some.product.url","n":"Product 1 Name","q":"1","sk":"sku number 1","st":"$499.99"},{"i":"https://some.image.URL 2","u":"https://some.product.url","n":"Product 2 Name","q":"1","sk":"sku number 2","st":"$499.99"}]'),
(N'[{"i":"https://some.image.URL 1","u":"https://some.product.url","n":"Product 1 Name","q":"1","sk":"sku number 1","st":"$499.99"},{"i":"https://some.image.URL 2","u":"https://some.product.url","n":"Product 2 Name","q":"1","sk":"sku number 2","st":"$499.99"}]')
Statements:
-- For fixed structure with two items
SELECT JSON_VALUE ([gca], '$[0].i') + ', ' + JSON_VALUE ([gca], '$[1].i') AS [http]
FROM #Data
-- For JSON array with multiple items and SQL Server 2017+
SELECT j.*
FROM #Data d
CROSS APPLY (
SELECT STRING_AGG([http], ',') AS [http]
FROM OPENJSON(d.[gca]) WITH ([http] varchar(max) '$.i')
) j
-- For JSON array with multiple items
SELECT STUFF(j.[http], 1, 1, N'') AS [http]
FROM #Data d
CROSS APPLY (
SELECT CONCAT(',', [http])
FROM OPENJSON(d.[gca]) WITH ([http] varchar(max) '$.i')
FOR XML PATH('')
) j([http])
Output:
-------------------------------------------------
http
-------------------------------------------------
https://some.image.URL 1,https://some.image.URL 2
https://some.image.URL 1,https://some.image.URL 2
Note, that JSON support was intoduced in SQL Server 2016 and STRING_AGG() was introduced in SQL Server 2017. For string aggregation in earlier versions use FOR XML PATH.
