Requête à partir du champ json imbriqué MySQL et renvoie un sous-ensemble de json

Question

J'ai les lignes suivantes :

id	Journal du produit	Temps de création
1	<chaîne json, voir ci-dessous>	2022-07-13 07:13:00

Exemples de données JSON :

{
    "products": [
        {
            "logs": [{
                "log_time": "2022-07-13 01:30:00",
                "log_type": "manual",
                "log_info": "some text"
            }],
            "product_id": 123,
            "time": "2022-07-12 01:30:00"
        }, 
        {
            "logs": [],
            "product_id": 124,
            "time": "2022-07-13 01:31:00"
        }
    ]
}

Par exemple, en recherchant product_id 124, la ligne entière devrait être renvoyée, mais le champ json ne contient que les objets correspondants :

id	Journal du produit	Temps de création
1	{"logs":[],"product_id":124,"time":"2022-07-13 01:31:00"}	2022-07-13 07:13:00

Ce serait également bien si le résultat résultant contenait la "forme" originale de json :

{
    "products": [
        {
            "logs": [],
            "product_id": 124,
            "time": "2022-07-13 01:31:00"
        }
    ]
}

Answer 1

SELECT test.id, 
       test.created_at, 
       JSON_OBJECT('products', JSON_ARRAYAGG(jsontable.log_data))
FROM test
CROSS JOIN JSON_TABLE(test.product_logs,
                      '$.products[*]' COLUMNS (log_data JSON PATH '$')) jsontable
WHERE jsontable.log_data->'$.product_id' = 124
GROUP BY 1,2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=26541a1e241be02680ba97a78f0791 c2一个>