This one is for @QMcKay as he’s the SQL genius… 
I’m using SQL Express 2017 and want to modify a custom product tag across from an SQL script. I have checked details online about the JSON_MODIFY function but the example JSON is in a different format than what SambaPOS uses. SambaPOS is storing each custom product tag with a set of TN and TV values. I’m not sure how to reference this using the JSON_MODIFY function or otherwise.
What I am trying to do - my client wants to have a “short name” for each product which I have created a custom product tag for, this is for printing on the receipt. They have already spent time to customise their POS screen and have updated all the menu item buttons Header value with a shorter name. To save time, I wanted to just take this Header value and save it to the “Short Name” custom product tag.
Their header name is always stored like <item number> <short name>, e.g. 12 S&S Chicken, so I am stripping off the number by taking the “short name” as everything after the first space.
Here is the SQL I have got, I just don’t know how to update the JSON field with custom product tags. Also, have to assume some products will already have a short name and some many not (i.e. they won’t have the “short name” custom product tag in the JSON). Apparently JSON_MODIFY function handles this nicely, if I can use it…
UPDATE MI
SET CustomTags = JSON_MODIFY(MI.CustomTags, "$.??????", SUBSTRING(SMI.Header, CHARINDEX(' ', SMI.Header) + 1, LEN(SMI.Header)))
FROM MenuItems MI
INNER JOIN ScreenMenuItems SMI ON MI.Id = SMI.MenuItemId
INNER JOIN ScreenMenuCategories SMC ON SMC.Id = SMI.ScreenMenuCategoryId
WHERE SMI.Header IS NOT NULL
AND SMI.Header != ''
AND SMC.ScreenMenuId = 1
GO
(where I have ?????? is where I am stuck with what to put, if this is even possible in this way?)
It would be nice to have a solution to edit via JSON like I suggest, but I am thinking since this is likely going to be a one time used script, I could hack it by just forcing a constant value into every “short name” custom product tag using Products > Product Tag Editor (like “zzzz”) then just use string manipulation in the SQL to replace “zzzz” with the short name from the header, thus bypassing the need to manipulate the JSON.
UPDATE: So I just did it this way, and it worked…
Add “zzzz” to “Short Name” custom product tag on all products you want to change using Products > Product Tag Editor.
Run the following SQL on the database:
UPDATE MI
SET CustomTags = REPLACE(MI.CustomTags, 'zzzz', SUBSTRING(SMI.Header, CHARINDEX(' ', SMI.Header) + 1, LEN(SMI.Header)))
FROM MenuItems MI
INNER JOIN ScreenMenuItems SMI ON MI.Id = SMI.MenuItemId
INNER JOIN ScreenMenuCategories SMC ON SMC.Id = SMI.ScreenMenuCategoryId
WHERE SMI.Header IS NOT NULL
AND SMI.Header != ''
AND SMC.ScreenMenuId = 1
GO
Go back to Products > Product Tag Editor, you will see the changes have been made. Now remove any leftover “zzzz” values (sort the column, then select all and press space then backspace to replace with an empty value).
Use CROSS APPLY or OUTER APPLY to “define” the JSON Keys, then you can use them like table “columns”, in your SELECT list, or in your WHERE clause…
SELECT
MI.[GroupCode]
,MI.[CustomTags]
,tagName
,tagValue
FROM [MenuItems] MI
INNER JOIN ScreenMenuItems SMI ON MI.Id = SMI.MenuItemId
INNER JOIN ScreenMenuCategories SMC ON SMC.Id = SMI.ScreenMenuCategoryId
CROSS APPLY OPENJSON(MI.[CustomTags]) WITH (tagName varchar(255) '$.TN', tagValue varchar(255) '$.TV') ct
WHERE 1=1
AND SMI.Header IS NOT NULL
AND SMI.Header != ''
AND SMC.ScreenMenuId = 1
AND tagName = 'productType'
AND tagValue = 'Food'
What that ^ does is, basically tagName becomes the JSON Key TN and tagValue becomes the JSON Value TV… the above query produces this:
The same sort of thing should work with an UPDATE statement, in the WHERE clause. But, as far as actually updating the column containing JSON, um, not sure, never tried that JSON_MODIFY function.
4 Likes
