Use object keys given by JSON_SEARCH to find a different key in the same object in mysql/mariadb

Use object keys given by JSON_SEARCH to find a different key in the same object in mysql/mariadb

I have some records of payments in the database and I keep the items belonging to the payment as a JSON(LONGTEXT) column in mariadb. For example the items columns would be like below.

items
[{"id":"a4","quantity":1,"title":"A4 Sheets","unitprice":7000,"total":7000},{"id":"reports","quantity":1,"title":"Reports","unitprice":750,"total":750},{"id":"other","quantity":2,"title":"Cap","unitprice":250,"total":500}]
[{"id":"exam3_5","quantity":1,"title":"Exam Fees : Grade 03 - 05","unitprice":750,"total":750},{"id":"a4","quantity":1,"title":"A4 Sheets","unitprice":7000,"total":7000},{"id":"reports","quantity":1,"title":"Reports","unitprice":750,"total":750}]
[{"id":"other","quantity":10,"title":"Test1","unitprice":1,"total":10},{"id":"other","quantity":10,"title":"Test2","unitprice":2,"total":20}]

what I want to do is find objects that have the id of "other" and get the total of each object.

나는 다른 사람들과 함께 물건을 얻을 수 있습니다.JSON_SEARCH(items, 'all', 'other')아래와 같은 결과를 제공합니다. 예를 들어 보겠습니다.첫 번째 표에 따르면 아닙니다.

JSON_SEARCH(items, 'all', 'other')
"$[2].id"
["$[0].id", "$[1].id"]

So i know which indexes of the array have i need to look at. Now i need to get the total of every object which has id as "other".

할 수 있어요JSON_EXTRACT(items, '$[*].total')그래서 나는

JSON_SEARCH(items, 'all', 'other')	JSON_EXTRACT(items, '$[*].total')
"$[2].id"	[7000, 750, 500]
["$[0].id", "$[1].id"]	[10, 20]

I need a third column something like this

JSON_SEARCH(items, 'all', 'other')	JSON_EXTRACT(items, '$[*].total')	Required Column
"$[2].id"	[7000, 750, 500]	500
["$[0].id", "$[1].id"]	[10, 20]	30

Finally I want a cumulative total of the "Required Column" which I guess I can do by SUM(Required Column).

Any advice on achieving the "Required Column"? I'm using nodejs as the backend. I could do the processing there but I want to know if it can be done with mysql itself. Thanks

ReferenceURL : https://stackoverflow.com/questions/73273548/use-object-keys-given-by-json-search-to-find-a-different-key-in-the-same-object