MySQL JSON：查找子数组中兄弟元素的值

1yjd4xko 于 2023-04-19 发布在 Mysql

关注(0)|答案(2)|浏览(135)

我在MariaDB 10.2中的JSON（LONGTEXT）列中有以下（伪）JSON

{"order":
    {"otherstuff":...},
    {"dates":
        [
            {
            "typeId":2,
            "date":"2019-05-21 09:00:00"
            },
            {
            "typeId":4,
            "date":"2019-05-21 10:00:00"
            }
        ]
    }
}

我需要的是订单的日期，同时我知道我需要哪种类型（4）。一个订单可以有许多日期，由它们的typeId标识。typeId 4并不总是在第二位置。

SELECT JSON_UNQUOTE(JSON_SEARCH(`json`, 'one', 4, NULL, '$.dates[*].typeId'))
// gives me: $.dates[1].typeId

我现在的第一个想法是REPLACE typeId加上date，但这会抱怨混合排序规则。
此外，查询应该是我的表中GENERATED列的表达式。由于日期id 4不一定对每个订单都存在，我尝试了这个：

SELECT IF(4 IN (JSON_EXTRACT(json, '$.dates[*].typeId')), 'yes', 'no')
// above condition evaluates to [2, 4]

我已经去掉了'['和']'，但是如果数组中的第一个是4，它只会给我一个'yes'（它是一个数组吗？）。所以（不带括号）：
[4, 7]-〉是
[2, 4]-〉否
我假设它不会被识别为一个值数组，而是一个字符串，那么为什么如果我的指针在第一个位置，它会给予我“是”呢？
而不是yes和no，我显然想使用日期和NULL。
MySQL的JSON函数对我来说很新，所以也许有人能给我指出正确的方向？

mysql

来源：https://stackoverflow.com/questions/56240310/mysql-json-finding-value-of-sibling-element-in-sub-array

2条答案

按热度按时间

nafvub8i1#

尝试：

选项1：

SELECT
  JSON_UNQUOTE(
    JSON_EXTRACT(
      `json`,
      REPLACE(
        JSON_UNQUOTE(
          JSON_SEARCH(
            `json`,
            'one',
            4,
            NULL,
            '$.order.dates[*].typeId'
          )
        ),
        'typeId',
        'date'
      )
    )
  ) `date`;

选项二：

SELECT
  IF(
    JSON_CONTAINS(
      JSON_EXTRACT(
        `json`,
        '$.order.dates[*].typeId'
      ),
      4
    ),
    'yes',
    'no'
  ) `exists`;

参见dbfiddle。

赞(0）回复(0）举报 2023-04-19

hmmo2u0o2#

以下解决方案适用于MySQL 8。
它假设一个表my_table，列jsonData看起来像这样：

{
    "dates": [
        {
            "type": "created",
            "dt": "2021-10-10"
        },
        {
            "type": "updated",
            "dt": "2021-10-11"
        },
    ]
}

TL;DR SQL：

SELECT 
  JSON_EXTRACT(
        JSON_EXTRACT(
            jsondata,
            '$.dates[*].dt'
        ),
        JSON_UNQUOTE(
            JSON_SEARCH(
                JSON_EXTRACT(
                        jsonData,
                        '$.dates[*].type'
                    ),
                'one',
                'updated' -- the date field to load
            )
        )
  ) as dateUpdated
from my_table

这是它的工作解决方案。

SELECT
    
    -- The first step is to extract all the date types as an array 
    JSON_EXTRACT(
            jsonData,
            '$.dates[*].type'
    ) as jsonDateTypesAsArray,
    
    -- We can get a companion array of values, where the position within the array is the same as the types array above
    JSON_EXTRACT(
            jsonData,
            '$.dates[*].dt'
        ) as jsonDateValuesAsArray,
        
    -- Next, we search the types array for the first date type     
    JSON_UNQUOTE(
        JSON_SEARCH(                        
            JSON_EXTRACT(                   -- This is jsonDateTypesAsArray above
                    jsonData,
                    '$.dates[*].type'
                ),
            'one',
            'updated'                       -- This is the date field to load
            )
        ) as jsonLocalPathOfMatchingDate,

    -- Now we can call JSON_EXTRACT on the payload to get the path in jsonLocalPathOfMatchingDate
    JSON_UNQUOTE(
      JSON_EXTRACT(
        JSON_EXTRACT(                     -- This is jsonDateValuesAsArray above
            jsonData,
            '$.dates[*].dt'
        ),
        JSON_UNQUOTE(                     -- This is jsonLocalPathOfMatchingDate above 
            JSON_SEARCH(
                JSON_EXTRACT(
                        jsonData,
                        '$.dates[*].type'
                    ),
                'one',
                'updated.'                -- Date field to load
            )
        )
      )
    ) as dateUpdated
from my_table

赞(0）回复(0）举报 2023-04-19

我来回答

MySQL JSON：查找子数组中兄弟元素的值

2条答案

相关问题

热门标签

最新问答