如何使用MySQL或R获取JSON包含特定文本的信息

Question

我在MySQL (Version =5.7.25)中有一个下面提到的表：

Tab1

ID               Data
YT-1             {JSON}
YT-2             {JSON}
YT-3             {JSON}

示例Json格式可用：https://codebeautify.org/online-json-editor/cb57a5ce

上面的表包含ID列，其中每个ID都是唯一的，Data列包含JSON字符串，对于每个ID，我想知道存储在Data列中的JSON字符串是否包含“接受”值。如果对任何特定的ID，JSON包含json字符串中的值“Accepted”，则将其显示为True，否则为False。

注意：

在JSON中，

1. 表示Status段可能有n个resp值，
2. 用于finalStatus段可以用于某些it，或者不能用于finalStatus段的
3. ，可以有一个可能的值，例如Not Accepted，在标志列H 218G 219中应该被认为是D17。

此外，在finalStatus段中可能有多个子段，如下所示：

{
      "report": "submitted",
      "order": "yearly",
      "special status" : "Accepted",
      "price": "4,451"
    },
{
      "report": "prepared",
      "order": "yearly",
      "special status" : "Not Accepted",
      "price": "4,451"
    },
{
      "report": "yet to start",
      "order": "yearly",
      "price": "4,451"
    }

所需输出

ID               Data      Flag
YT-1             {JSON}    True
YT-2             {JSON}    False  
YT-3             {JSON}    check it

Answer 1

JSON的结构不适合搜索。不过，您可以使用JSON_SEARCH函数，它允许您在指定的位置搜索特定的字符串：

SELECT id,
       CASE WHEN JSON_SEARCH(data, 'one', 'Not Accepted', NULL, '$.finalStatus[*]."special status"') IS NOT NULL THEN 'check it'
            WHEN JSON_SEARCH(data, 'one', 'Accepted', NULL, '$.Status', '$.Status1', '$.Status2') IS NOT NULL THEN 'True'
            ELSE 'False'
       END AS flag
FROM t

上面的内容将在finalStatus数组中搜索包含值Not Accepted的键special status。然后，它将在Status、Status1和Status2中搜索包含值Accepted的键。根据你的需要修改道路。