1

I need to write a SQL query that will take stored JSON in a table column and return just the array element. Problem is I can't use a JSON parser for this due to it being SQL Azure. I take it I will have to do this using regex.

Here is my Json stored in SQL

[{"IsPrimary":false,"Address":"[email protected]","Type":"Other"}]

I want to select out just the email address "[email protected]" only.

allencoded
  • 7,015
  • 17
  • 72
  • 126

1 Answers1

0

If you know upfront that your JSON string across all the rows will have the same "schema" / name:value pairs then yes, Regex might be the way to go.

On the other hand, if you are looking for a JSON deserializer inside of SQL Server, then check out this article that has the source code for a parseJSON() function for deserializing JSON.

Simple-Talk.com: Consuming JSON Strings in SQL Server

Might be definitely over-kill for what you are trying to do, but just so you know, it is possible and the source code works (I tried it on my localhost SQL Server on a variety of nested JSON strings.

Shiva
  • 20,575
  • 14
  • 82
  • 112
  • It will be the same across the board. – allencoded Nov 06 '13 at 19:08
  • SQL Server does not have built-in Regex support (you may be aware of this.). So your option is to either 1) write a CLR Function that does the Regex processing or 2) Try PATINDEX for simple Pattern matching and do a Substring. For #1, see here. http://msdn.microsoft.com/en-us/magazine/cc163473.aspx For #2, you'll have to try it out (trial and error). Not sure how it will work for empty values of "address". – Shiva Nov 06 '13 at 19:30