SQL 2016: Query by Ticket Tag

V5 Question
1

Not really a SambaPOS question, more of an SQL 2016, but I hope @QMcKay or others might be able to help…

I have a ticket tag called “Ticket Reference” and it allows me to group several tickets together when several customers pay as part of a bigger group or online booking.

I am trying to work out how I can select tickets using the “TicketReference” in SQL 2016…

The JSON data would look like:

[  
   {  
      "TN":"TicketReference",
      "TT":0,
      "TV":"287"
   },
   {  
      "TN":"PostPaidProcessing",
      "TT":0,
      "TV":"Flagged"
   }
]

I know I can extract the ticket reference using:

SELECT TicketNumber, Date, TotalAmount, JSON_VALUE(TicketTags, '$[0].TV')
FROM Tickets

But this will only work where the TickerReference is the first ticket tag stored, and I won’t know in which order all the various tags might be stored. So how can I reliably always select the TicketReference JSON value, rather than arbitarilly using just the first element of the JSON string?

2

The trick is to use CROSS APPLY. Search for that and you will see some examples in a few topics. This one is a good example …

https://forum.sambapos.com/t/sql-server-express-2016/11605/13

3

Ahhhh - Amazing. Thanks @QMcKay

This works like a charm…

SELECT Id, TicketNumber, Date, TotalAmount, TagName, TagValue
  FROM Tickets
  CROSS APPLY OPENJSON(TicketTags)
  WITH (
   TagName	varchar(50) '$.TN'
  ,TagValue varchar(50) '$.TV'
  )
  WHERE TagName = 'TicketReference' and TagValue = '315'