Environment: SQL Server 2014 and above
How do I access the email value in my JSON value with my SELECT statement?
select JSON_VALUE('[{"data":{"email":"[email protected]"}}]', '$.email') as test
5 Answers
Json support was only introduced in SQL Server 2016 - so with any prior version you would need to either use string manipulation code or simply parse the json outside of SQL Server (maybe using a CLR function)
For 2016 version or higher, you can use JSON_VALUE like this:
declare @json as varchar(100) = '[{"data":{"email":"[email protected]"}}]';
select JSON_VALUE(@json, '$[0].data.email') as test
For older versions - you might be able to get away with this, but if your json value does not contain an email property, you will get unexpected results:
select substring(string, start, charindex('"', string, start+1) - start) as test
from (
select @json as string, charindex('"email":"', @json) + 9 as start
) s
You can see a live demo on db<>fiddle
2 Comments
Rod
What if there were more than one object in the JSON, how do you display all of them in table format?
Zohar Peled
For 2014 version you either handle the json outside SQL Server, or use Alan Burstein or Shnugo's answer. Mine is just a simple workaround. For 2016 or higher you use
OPENJSON with a with clause.Another way. PatternSplitCM is great for stuff like this.
Extract a single Email value:
DECLARE @json as varchar(200) = '[{"data":{"email":"[email protected]"}}]';
SELECT f.Item
FROM dbo.patternsplitCM(@json,'[a-z0-9@.]') AS f
WHERE f.item LIKE '%[a-z]%@%.%[a-z]%'; -- Simple Email Check Pattern
Extracting all Email Addresses (if/when there are more):
DECLARE @json VARCHAR(200) = '[{"data":{"email":"[email protected]"},{"email2":"[email protected]"}},{"data":{"MoreEmail":"[email protected]"}}]';
SELECT f.Item
FROM dbo.patternsplitCM(@json,'[a-z0-9@.]') AS f
WHERE f.item LIKE '%[a-z]%@%.%[a-z]%'; -- Simple Email Check Pattern
Returns:
Item
--------------------------
[email protected]
[email protected]
[email protected]
Or... the get only the first Email address that appears:
SELECT TOP (1) f.Item
FROM dbo.patternsplitCM(@json,'[a-z0-9@.]') AS f
WHERE f.item LIKE '%[a-z]%@%.%[a-z]%' -- Simple Email Check Pattern
ORDER BY ROW_NUMBER() OVER (ORDER BY f.ItemNumber)
Nasty fast, super-simple. No cursors, loops or other bad stuff.
Comments
It can be done in two ways:
First, if your JSON data is between [ ] like in your question:
select JSON_VALUE('[{"data":{"email":"[email protected]"}}]','$[0].data.email' ) as test
And if your JSON data is not between [ ]:
select JSON_VALUE('{"data":{"email":"[email protected]"}}','$.data.email' ) as test
You can teste the code above here
Comments
With v2014 there is no JSON support, but - if your real JSON is that simple - it is sometimes a good idea to use some replacements in order to transform the JSON to XML like here, which allows for the native XML methods:
DECLARE @YourJSON NVARCHAR(MAX)=N'[{"data":{"email":"[email protected]"}}]';
SELECT CAST(REPLACE(REPLACE(REPLACE(REPLACE(@YourJSON,'[{"','<'),'":{"',' '),'":"','="'),'}}]',' />') AS XML).value('(/data/@email)[1]','nvarchar(max)');
answered Sep 4, 2019 at 14:51
Gottfried Lesigang
67.6k9 gold badges60 silver badges124 bronze badges
2 Comments
Zohar Peled
All these replaces makes a cumbersome solution but well worth it to be able to use SQL Server's powerful built in XML support (Hopefully Json support will get a boost to match it soon)
Gottfried Lesigang
@ZoharPeled So true! JSON is quite mighty already, but it's lacking the real JSON data type (it's just a special kind of
nvarchar(max)) and some higher support of json-path. That's the reason, why expensive parsing must be done over and over and many operations are just impossible. And true too, all these replacements are cumbersome ;-)Your query should be like this (SQL Server 2016):
DECLARE @json_string NVARCHAR(MAX) = 'your_json_value'
SELECT [key],value
FROM OPENJSON(@json_string, '$.email'))
UPDATE :
select JSON_VALUE(@json_string, '$[0].data.email') as test
6 Comments
Thom A
Unfortunately, we have discovered the OP is using 2014. the fact that the OP had
JSON_VALUE in their attempt confused us all; as they hadn't mentioned that the above would have errored with an unknown function.Thom A
JSON_VALUE doesn't exist in 2014.Thom A
What is a typo?
JSON_VALUE? select CHARINDEX(@json_string, '$[0].data.email') as test won't work. |
OPENJSON?JSON_VALUE(also added in 2016) I assumed you had 2016+.