3

First of all before I get into the question, I'll preface this with the fact that I know that this is a "bad" idea. But for business reasons it is something that I have to come up with a solution to, and I'm hoping that someone, somewhere might have some ideas on how to go about this.

I have a SQL Server 2008 R2 table that has a "OtherProperties" column. This column contains various other, somewhat arbitrary additional pieces of information that relate to the records. There is a business need to create a UDF that we can use to query the results, for example.

SELECT *
FROM MyTable
WHERE MyUDFGetValue(myTable.OtherProperties, "LinkedOrder[0]") IS NOT NULL

This would find a record where there was an array of LinkedOrder entries that contained a value at index 0

SELECT *
FROM MyTable
WHERE MyUDFGetValue(myTable.OtherProperties, "SubOrder.OrderId") = 25

This would find a property "orderId" and use its value in a comparison.

Anyone seen an implementation of this? I've seen implementations of functions. Like this JSONParser that take the values into a table which just will not get us what we need query wise. Complexity wise, I don't want to write a full fledged JSON parser, but I can if I need to.

Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
  • possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) – bummi Jun 25 '15 at 07:11

3 Answers3

2

It's been a long time since you asked your question but there is now a solution you can use - JSON Select which provides various functions for different datatypes, for example the JsonInt() function. From one of your examples (assuming OrderId is an int, if not you could use a different function):

SELECT *
FROM MyTable
WHERE dbo.JsonInt(myTable.OtherProperties, 'SubOrder.OrderId') = 25

DISCLOSURE: I am the author of JSON Select, and as such have an interest in you using it :)

joshuahealy
  • 3,529
  • 22
  • 29
2

Not sure if this will suit your needs but I read about a CLR JSON serializer/deserializer. You can find it here, http://www.sqlservercentral.com/articles/CLR/74160/

Ally Reilly
  • 282
  • 3
  • 15
1

If you cannot use SQL Server 2016 with built-in JSON support, you would need to use CLR e.g. JSONselect, json4sql, or custom code such as http://www.codeproject.com/Articles/1000953/JSON-for-SQL-Server-Part, etc.

Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55