JSON_VALUE for Nameless JSON payload

Question

Just started playing with JSON_VALUE in SQL Server. I am able to pull values from name/value pairs of JSON but I happen to have an object that looks like this:

["[email protected]"]

When I attempt what works for name/value pairs:

SELECT TOP 1
    jsonemail,
    JSON_VALUE(jsonemail, '$') as pleaseWorky
FROM MyTable

I get back the full input, not [email protected]. Am I out of luck? I don't control the upstream source of the data. I think its a sting collection being converted into a json payload. If it was name: [email protected] I would be able to get it with $.name.

Thanks in advance.

Answer 1

It is a JSON array. So you just need to specify its index, i.e 0.

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, jsonemail NVARCHAR(MAX));
INSERT INTO @tbl (jsonemail) VALUES
('["[email protected]"]');
-- DDL and sample data population, end

SELECT ID
   , jsonemail AS [Before]
   , JSON_VALUE(jsonemail, '$[0]') as [After]
FROM @tbl;

Output

+----+---------------------------+-----------------------+
| ID |          Before           |         After         |
+----+---------------------------+-----------------------+
|  1 | ["[email protected]"] | [email protected] |
+----+---------------------------+-----------------------+

Answer 2

From the docs:

• Array elements. For example, $.product[3]. Arrays are zero-based.

So you need JSON_VALUE(..., '$[0]') when the root is an array and you want the first value.

---

To break it out into rows, you would need OPENJSON:

SELECT TOP 1
   jsonemail
   ,j.[value] as pleaseWorky
FROM MyTable
CROSS APPLY OPENJSON(jsonemail) j