MariaDB query columns into JSON

Question

I've been going around in circles for a while and just not sure how to tackle this using the available JSON functions. I have a database table with item names and a count for a player identifier and I'd like to represent this set of records as a single json object keyed by the item name. THis is what the table data looks like.

This is the structure I want to produce:

{
  "WEAPON_PETROLCAN":1,
  "water":1,
  "bandage":2,
  "WEAPON_PISTOL":1
}

This is my starting point:

SELECT *
FROM addon_inventory_items 

WHERE inventory_name ='property'
  AND owner LIKE 'license:ee7d%';

I've tried using JSON_INSERT with a previously defined @json variable but its not being formed correctly. Anyone know how to do this? My dbms version is 10.5.8

The background is that I want an update and insert trigger to respond in each case a create a snapshot of a player's property inventory state which I can then store on a history audit table with a timestamp. I want to capture this json object in as a variable to then use in a subsequent INSERT statement.

user_2469308 · Answer

This can be very easily achieved by using JSON_OBJECTAGG function. Note that you need to have MariaDB version atleast 10.5.0 or above, to use it.

In case, you are using MySQL, both 5.7 and 8.0 support it. Ref: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-objectagg

So, your query would look like:

SELECT owner, JSON_OBJECTAGG(name, count)
FROM addon_inventory_items 

WHERE inventory_name ='property'
  AND owner LIKE 'license:ee7d%'
GROUP BY owner

MariaDB query columns into JSON

1 Answers