mysql - 基于优先级的SQL查询案例

Question

我有一个用户表，其中包含 user_id 和 user_details 等列。 user_details 列中包含字符串格式的 JSON 数据，如下所示:

"[{"value":"sachin","label":"What's your first name?"},{"value":"test@example.com","label":"What's your email?"},{"value":"+911234567890","label":"What's your phone number?"},{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

它将包含 user_details 列中名称和值对的数据，如上所示。

我想要一个 SQL 查询来从 user_details 列中查找值，如果标签包含“姓名”或“电子邮件”或“电话号码”，则显示相应的值。示例 - 1:

"[{"value":"sachin","label":"What's your first name?"},{"value":"test@example.com","label":"What's your email?"},{"value":"+911234567890","label":"What's your phone number?"},{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

在此示例中，SQL 查询应产生以下输出:

名称 |萨钦

示例 - 2:

"[{"value":"test@example.com","label":"What's your email?"},{"value":"+911234567890","label":"What's your phone number?"},{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

这里应该是:电子邮件 | test@example.com

示例 - 3:

"[{"value":"+911234567890","label":"What's your phone number?"},{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

这里应该是:电话 | +911234567890

示例 - 4:

"[{"value":"xyz","label":"What's your city?"},{"value":"abc","label":"What's your address?"}]"

这里应该是:编号 | 5(在此示例中，我们没有姓名、电子邮件或电话号码，那么查询应返回 id，即该行的主键。)

我尝试使用案例查询，但似乎不起作用。有没有办法得到这个输出？

Answer 1

正如其他人所说，您确实应该更改数据库架构。但是，如果您坚持使用当前设置，此查询可能会满足您的需求:

SELECT
  CASE WHEN json_search(user_details, 'one', '%name%', null, '$[*].label')
         IS NOT NULL
       THEN 'name'
       WHEN json_search(user_details, 'one', '%email%', null, '$[*].label') 
         IS NOT NULL
       THEN 'email'
       WHEN json_search(user_details, 'one', '%phone number%', null, '$[*].label') 
         IS NOT NULL
       THEN 'phone'
       ELSE 'id'
  END type,
  CASE WHEN json_search(user_details, 'one', '%name%', null, '$[*].label') 
         IS NOT NULL
       THEN json_unquote(json_extract(user_details, 
              concat(
                json_unquote(
                  replace(
                    json_search(user_details, 'one', '%name%', null, '$[*].label'), 
                    '.label', '')),
                '.value')))
       WHEN json_search(user_details, 'one', '%email%', null, '$[*].label') 
         IS NOT NULL
       THEN json_unquote(json_extract(user_details, 
              concat(
                json_unquote(
                  replace(
                    json_search(user_details, 'one', '%email%', null, '$[*].label'), 
                    '.label', '')),
                '.value')))

       WHEN json_search(user_details, 'one', '%phone number%', null, '$[*].label') 
         IS NOT NULL
       THEN json_unquote(json_extract(user_details, 
              concat(
                json_unquote(
                  replace(
                    json_search(user_details, 'one', '%phone%', null, '$[*].label'), 
                    '.label', '')),
                '.value')))
       ELSE user_id
  END value
FROM json_user;