如何将数据库中的单个字段中的list数据在查询时拆分成对应的多个字段(单行)数据

儒曦

已于 2024-01-24 18:59:56 修改

阅读量498

点赞数 9

分类专栏：数据库文章标签：数据库

于 2024-01-24 18:58:24 首次发布

本文链接：https://blog.csdn.net/weixin_43491259/article/details/135829253

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

示列

pt_info
{“pt_name”:“患者姓名”,“pt_phone”:“15011111111”,“pt_gender”:“男”,“pt_age”:“61”,“pt_addr”:“北京市”,“pt_disease”:“测试”,“is_medicine”:“未服药”,“pay_type”:[“本地医保卡”],“buy_type”:[“医院购药”],“help”:[“挂号加号”],“ptRealName”:“患者姓名”,“medicine_info”:“尚未就诊”,“first_time”:“从未就诊”,“is_medicine”:“未服药”}

pt_info

{“pt_name”:“患者姓名”,“pt_phone”:“15011111111”,“pt_gender”:“男”,“pt_age”:“61”,“pt_addr”:“北京市”,“pt_disease”:“测试”,“is_medicine”:“未服药”,“pay_type”:[“本地医保卡”],“buy_type”:[“医院购药”],“help”:[“挂号加号”],“ptRealName”:“患者姓名”,“medicine_info”:“尚未就诊”,“first_time”:“从未就诊”,“is_medicine”:“未服药”}

在查询的时候就拆分成单行

SELECT
  mc.dr_id,
  mc.dr_name as '医生姓名',
  mc.pt_id as '患者id',
  mc.pt_name as '患者名称',
  mc.pt_real_name as '患者真实姓名',
  sub.sender_count dr_count ,
  sub1.sender_count1 pt_count,
  //下面就是拆分的list 数据
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pt_phone'))
    ELSE NULL
  END AS pt_phone,
  CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pt_gender'))
    ELSE NULL
  END AS pt_gender,
  CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pt_age'))
    ELSE NULL
  END AS pt_age,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pt_addr'))
    ELSE NULL
  END AS pt_addr,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pt_disease'))
    ELSE NULL
  END AS pt_disease,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.is_medicine'))
    ELSE NULL
  END AS is_medicine,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.pay_type'))
    ELSE NULL
  END AS pay_type,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.buy_type'))
    ELSE NULL
  END AS buy_type,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.help'))
    ELSE NULL
  END AS help,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.medicine_info'))
    ELSE NULL
  END AS medicine_info,
	CASE
    WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, '$.first_time'))
    ELSE NULL
  END AS first_time
 
FROM
  med_conv mc
  LEFT JOIN med_conv_pt_info mcpi ON mc.pt_id = mcpi.pt_id
  LEFT JOIN (
    SELECT
      sender_id,
   receiver_id,
   is_gpt,
   is_collect,
      COUNT(*) AS sender_count
    FROM
      med_conv_msg mcm
    GROUP BY
      sender_id,receiver_id,is_gpt,is_collect
  ) sub ON mc.dr_id = sub.sender

CASE
WHEN JSON_VALID(mcpi.pt_info) THEN JSON_UNQUOTE(JSON_EXTRACT(mcpi.pt_info, ‘$.pt_phone’))
ELSE NULL
END AS pt_phone,

可以通过以上的sql语句将list 拆分成单个数据

当处理 JSON 数据时，MySQL 提供了一些特定的函数来操作和提取 JSON 内容。在上面的语句中，我们使用了以下函数：

JSON_VALID：这个函数用于检查给定的字符串是否是有效的 JSON。如果字符串是有效的 JSON，函数将返回 1，否则返回 0。在这个查询中，我们使用 JSON_VALID 来检查 pt_info 字段是否是有效的 JSON 数据。

JSON_EXTRACT：这个函数用于提取 JSON 字符串中指定 JSON 路径表达式所对应的值。语法是 JSON_EXTRACT(json_doc, json_path)，其中 json_doc 是要提取值的 JSON 字符串，json_path 是要匹配的 JSON 路径表达式。在这个查询中，我们使用 JSON_EXTRACT 来提取 pt_phone 属性的值。

JSON_UNQUOTE：这个函数用于去除 JSON 字符串的引号，返回不带引号的字符串。在这个查询中，我们使用 JSON_UNQUOTE 来去除 pt_phone 属性值所包含的引号。

通过这些函数的组合使用，我们可以在有效的 JSON 数据中提取指定属性的值，并在属性值存在的情况下返回它们，否则返回 NULL。这样可以确保在查询结果中只获取有效的属性值，而忽略无效或缺失的数据。

此外，若需要拓展相关知识，你可以学习更多与 MySQL JSON 函数相关的概念和用法。这些函数可以让你更方便地处理和查询存储为 JSON 的数据。你可以了解如何使用索引、过滤和操作 JSON 数据，以及更高级的操作例如修改和合并 JSON 文档等。