检查一个元素是否包含在MySql的json列的值(数组)中 [英] Check if an element is contained in the values ​(array) of a json column in MySql

问题描述

我在MySql的json列的单元格内有以下值:

I have the following values ​​inside a cell of a json column in MySql:

{
    "produttori": [
        "8",
        "9"
    ],
    "articoli_alternativi": [
        "3",
        "9"
    ],
    "articoli_accessori": [
        "5",
        "6",
        "7",
        "8"
    ],
    "tecnologie": [],
    "fornitori": [
        "9",
        "8"
    ],
    "classificazioni": [
        "3",
        "4"
    ]
}

我想执行一个查询，该查询基于 fornitori 键上数组中值的存在来提取数据.
现在，我已经尝试过了:

I would like to make a query that extracts data based on the existence of a value in the array at the fornitori key.
For now I've tried this:

query = 'SELECT nome, formulati_commerciali FROM articolo WHERE JSON_CONTAINS(JSON_EXTRACT(dati, "$.fornitori"), "' + \
        value+'", "$")'

哪个印刷品是:

SELECT name, data FROM articolo WHERE JSON_CONTAINS(JSON_EXTRACT(data, "$.fornitori"), "8", "$")

基本上，条件是 value ("8" )必须在 fornitori 列表内，否则跳过该元素.
不幸的是，该查询没有产生任何结果.
我想知道如何在MySql中制定这样的查询.我会经常需要它们！

预先感谢！

Basically the condition is that value ("8") must be inside the fornitori list, otherwise skips the element.
Unfortunately, the query did not produce any results.
I would like to know how you can formulate such a query in MySql. I will need them often!

Thanks in advance!

推荐答案

这应该做到:

SELECT name, data 
FROM articolo 
WHERE JSON_CONTAINS(data, '"8"', '$.fornitori')

8 周围的双引号很重要，以便正确匹配JSON数据.另一方面，查询始终对字符串文字使用单引号.

The double quotes around 8 are important, in order to properly match the JSON data. On the other hand, the query consistently uses single quotes for string literals.

这篇关于检查一个元素是否包含在MySql的json列的值(数组)中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！