MySQL JSON_OBJECT instead of GROUP_CONCAT

Question

I have the following sql query that works fine using GROUP_CONCAT:

SELECT orders.created_at,products.title, o_p.qty AS qty,

    (SELECT GROUP_CONCAT(features.title,"\:", o_p_f.value, features.unit) FROM order_product_features AS o_p_f 
     LEFT JOIN product_features ON product_features.id = o_p_f.product_feature_id
    LEFT JOIN features ON o_p_f.product_feature_id = product_features.id
     AND features.id = product_features.feature_id

     WHERE o_p_f.order_product_id = o_p.id
) AS prop
FROM orders
LEFT JOIN order_products AS o_p ON o_p.order_id = orders.id

LEFT JOIN products ON products.id = o_p.product_id

The above query returns result looks like the following screen shot:

Now, I want to replace GROUP_CONCAT with JSON_OBJECT or in other words, I want to have prop field to be JSON object. I have tried the following:

SELECT orders.created_at,products.title, o_p.qty AS qty,

    JSON_OBJECT((SELECT GROUP_CONCAT("title",features.title,"value", o_p_f.value, 'unit',features.unit) FROM order_product_features AS o_p_f 
     LEFT JOIN product_features ON product_features.id = o_p_f.product_feature_id
    LEFT JOIN features ON o_p_f.product_feature_id = product_features.id
     AND features.id = product_features.feature_id

     WHERE o_p_f.order_product_id = o_p.id
)) AS prop
FROM orders
LEFT JOIN order_products AS o_p ON o_p.order_id = orders.id

LEFT JOIN products ON products.id = o_p.product_id

However, the above query returns error:

1582 - Incorrect parameter count in the call to native function 'JSON_OBJECT'

Timid

minimal reproducible example please... – Am‚lie

@Am‚lie Simply, as I have regarded in the question "...or in other words, I want to have prop field to be JSON object" i.e I need a way to transform the prop field into a JSON object. Any example you will supply that use GROUP_CONCAT then using any way to transform that concatenated string into JSON will be very enough to solve my problem. – Timid

Please, just provide some test data and your create table, we can't try anything without... – Am‚lie

According to the docs, JSON_OBJECT takes an even number of params corresponding to key1, value1, key2, value2, etc. You’re passing it one param, the string returned by GROUP_CONCAT. – Sierra

I would try eliminating the call to group_concat. You don’t seem to have a group by clause so I wonder why you’re using group_concat at all rather than say concat. I would think json_object(select “title”, features.title, “value”, ...etc) would be the way to go. – Sierra

Answer 1

On ≥ 5.7.22

JSON_OBJECTAGG(key, value) is more likely what you're after.

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value) FROM t3 GROUP BY o_id;
+------+----------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, name)        |
+------+----------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}     |
|    3 | {"color": "green", "shape": "square"}  |
+------+----------------------------------------+
1 row in set (0.00 sec)

But as you don't provide your table structure I can't help you with the query.

It seem your handling of unit will grant you some extra work, as aggregate function use Key=>Value and will not take a third argument...

On ≥ 5.7

You'll have to make a bit of hand craft:

SELECT 
o_id, 
CONCAT(
    '{', 
    GROUP_CONCAT(
        TRIM(
            LEADING '{' FROM TRIM(
                TRAILING '}' FROM JSON_OBJECT(
                    `attribute`, 
                    `value`
                    )
                )
            )
        ),
    '}'
    ) json 
FROM t3 
GROUP BY o_id

≥ 5.5

Without any JSON function:

SELECT 
o_id, 
CONCAT(
    '{', 
    GROUP_CONCAT(
        CONCAT(
            '"',
            `attribute`,
            '":"',
            `value`,
            '"'
            )
        ),
    '}'
    ) json 
FROM t3 
GROUP BY o_id 

Am‚lie

It is good idea, however, this function just added since MySQL 5.7.22, and it may be, much probably, not found at many shared hosts nowadays. – Timid

@Timid updated with versions for ≥5.7 and ≥5.5. (don't forget to flag your question as solved if it's ok) – Am‚lie

Don't forget to escape your values, or else it would be a huge source of bugs. (For example when your text contains "-s) – Arlyn