2

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.

enter image description here

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Stephen York
  • 1,247
  • 1
  • 13
  • 42
  • @MadhurBhaiya Sorry, I'd written out the json wrong, it's not individual json objects, I've updated the question to represent what I need. The version is there now – Stephen York Mar 07 '21 at 04:45

1 Answers1

1

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
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • 1
    Legend thank you so much. I'd actualyl just got it working with ```SELECT @json := JSON_INSERT(@json, CONCAT('$.', name), count)``` but it does result in a separate result set being dhown plus requires a variable to be updated for each row. Don't know if that's a performance hit but I like your approach better. Thanks again. – Stephen York Mar 07 '21 at 04:53
  • @StephenYork using custom concatenation for creating JSON is error-prone, as lot of special characters like backslash, double quotes etc are not automatically handled. – Madhur Bhaiya Mar 07 '21 at 04:54
  • I wasn't specifying special characters though, the engine was putting those in. The concatenation was just for the top level path. I agree though, didnt' want to use that. – Stephen York Mar 07 '21 at 04:56