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'

@Blag 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. — SaidbakR
– SaidbakR, Commented Feb 17, 2018 at 18:24
Please, just provide some test data and your create table, we can't try anything without... — Blag
– Blag, Commented Feb 17, 2018 at 18:26
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. — James
– James, Commented Feb 17, 2018 at 18:30
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. — James
– James, Commented Feb 17, 2018 at 18:51

Community · Accepted Answer · 2020-06-20 09:12:55Z

17

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 17, 2018 at 18:50

Blag

5,8942 gold badges26 silver badges48 bronze badges

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

3 Comments

SaidbakR Over a year ago

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.

Blag Over a year ago

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

Ambrus Tóth Over a year ago

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

Collectives™ on Stack Overflow

MySQL JSON_OBJECT instead of GROUP_CONCAT

1 Answer 1

On ≥ 5.7.22

On ≥ 5.7

≥ 5.5

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

On ≥ 5.7.22

On ≥ 5.7

≥ 5.5

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related