JSON database field queries

Version 4 V4 Question
1

Is there anyway to query parts of the JSON fields in the database (like Custom Data & Order tags)?

For example if I wanted to find all orders that have a specific order tag, or all customers with a phone-number starting with a certain sequence…

2

I’m working on some custom report tags for preparing such reports easily.

3

So it’s not possible in V3?
Are you going to change the structure of the database, or how will you accomplish selecting based on partial content of JSON fields?

4

I’ve been experimenting with a query to get all orders with an order tag from a certain order tag group included (and show all its tags), and this is as far as I got:

SELECT O.Id, O.ExtraTopping1
FROM
(
SELECT ID,
STUFF(STUFF(OrderTags, 1, CHARINDEX(‘extra toppings’, OrderTags) + LEN(‘extra toppings’) + 7, ‘’), CHARINDEX(‘"’,STUFF(OrderTags, 1, CHARINDEX(‘extra toppings’, OrderTags) + LEN(‘extra toppings’) + 7, ‘’)), LEN(STUFF(OrderTags, 1, CHARINDEX(‘extra toppings’, OrderTags) + LEN(‘extra toppings’) + 7, ‘’)),‘’) AS ExtraTopping1,
OrderTags as Ot
FROM
Orders
) AS O
WHERE O.ExtraTopping1 is not null

This gives me:

The problem with this is that you can have an unlimited number of order tags for each order tag group, so this kind of query could never be complete…

That’s why I don’t see how you can implement it with custom report tags, unless you either change the database structure or there’s a better way to do this kind of query?

5

You could run a WHILE loop to get them all, no matter how many Tags are in the column.

6

Thanks for the tip. I’ll try that…