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.

@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
– Stephen York, Commented Mar 7, 2021 at 4:45

Madhur Bhaiya · Accepted Answer · 2021-03-07 04:47:02Z

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

answered Mar 7, 2021 at 4:47

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stephen York Over a year ago

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.

Madhur Bhaiya Over a year ago

@StephenYork using custom concatenation for creating JSON is error-prone, as lot of special characters like backslash, double quotes etc are not automatically handled.

Stephen York Over a year ago

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.

Collectives™ on Stack Overflow

MariaDB query columns into JSON

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related