主页 / dba / 问题 / 192208
Accepted
johnny
Asked: 2017-12-02 12:44:20 +0800 CST

MySQL：从标准 SQL 查询返回 JSON

  • 772

我已阅读有关 JSON 对象和 JSON 对象类型的信息。我只想做一个选择，它返回 JSON。我不一定要存储 JSON 对象。序列化本身不是我的问题。这些列是常规 Varchar、Int 等列，没有 JSON 对象，“正常”数据库行。

我可以做一个常规的旧 SELECT 并为 MySQL 返回 JSON 吗？

这不是SQL SERVER 中的FOR JSON和 PostgreSQL 中的rows_for_json所做的吗？

他们似乎在这方面领先，但我不想欺骗自己。

我从 2016 年发现了这个问题： https ://stackoverflow.com/questions/35324795/mysql-5-7-return-row-as-json-using-new-json-features

mysql json

5 个回答

  1. 将一行转换为 json

    在我看来，您不想聚合 JSON。你说你想要相当于row_to_json，如果是这样，那么我建议检查一下更简单的JSON_OBJECT

    SELECT JSON_OBJECT(
  'name_field', name_field,
  'address_field', address_field,
  'contact_age', contact_age
)
FROM contact;

    聚合 JSON

    附带说明一下，如果您确实需要将结果集聚合到 json。那么即将推出的 MySQL 8 将为您做到这一点。

    • 23
  2. Best Answer

    花了一点时间弄清楚（更习惯于 PostgreSQL，那里的事情要容易得多！），但是如果您在此处查阅精美的手册，下面12.16.2 Functions That Create JSON Values有该JSON_ARRAY功能，但实际上并没有多大用处 - 至少在这种情况下！

    要回答这个问题"select and it return JSON"，有两种方法可以做到这一点，都相当痛苦！

    你可以

    • 使用“hack” - 请参阅此处的 db-fiddle ，

    • 或者在这里使用 MySQL 提供的新 JSON 函数之一——具有讽刺意味的是，这似乎比 hack 本身更像是一种 hack！只有 MySQL ！:-) （在这里小提琴）。

    两个答案都使用了 MySQLGROUP_CONCAT函数——这篇文章有帮助。您可能希望将group_concat_max_len系统变量设置为比其默认值多一点（微不足道的 1024）！

    正如您可以想象的那样，第一个查询是混乱的（DDL并且DML在此答案的底部）：

    SELECT CONCAT('[', better_result, ']') AS best_result FROM
(
SELECT GROUP_CONCAT('{', my_json, '}' SEPARATOR ',') AS better_result FROM
(
  SELECT 
    CONCAT
    (
      '"name_field":'   , '"', name_field   , '"', ',' 
      '"address_field":', '"', address_field, '"', ','
      '"contact_age":'  , contact_age
    ) AS my_json
  FROM contact
) AS more_json
) AS yet_more_json;

    结果：

    [{"name_field":"Mary","address_field":"address one","contact_age":25},{"name_field":"Fred","address_field":"address two","contact_age":35},{"name_field":"Bill","address_field":"address three","contact_age":47}]

    这是正确的，但是，让我们面对现实吧，有点噩梦！

    然后是 MySQLJSON_ARRAY()方法（甚至更混乱 - 感谢 MySQL 的（不存在的）TRANSLATE()函数实现！）。

    SELECT
CONCAT
('[', REPLACE
  (
    REPLACE
    (
      GROUP_CONCAT
      (
        JSON_ARRAY
        (
          'name_field:', name_field, 
          'address_field:', address_field, 
          'age_field:', contact_age
        ) SEPARATOR ','
      ), '[', '{'
    ), ']', '}'
  ), ']'
) 
AS best_result2 
FROM contact

    结果一样！

    ====  TABLE CREATION and INSERT DDL and DML ============

    CREATE TABLE contact
    (
         name_field VARCHAR  (5) NOT NULL,
      address_field VARCHAR (20) NOT NULL,
      contact_age   INTEGER      NOT NULL
    );

    INSERT INTO contact
    VALUES
    ('Mary', 'address one',   25),
    ('Fred', 'address two',   35),
    ('Bill', 'address three', 47);
    • 17

  3. 对于 JSON 对象数组（查询中每行一个对象），您可以这样做：

    SELECT JSON_ARRAYAGG(JSON_OBJECT("fieldA", fieldA, "fieldB", fieldB)) 
  FROM table;

    这将产生一个包含所有条目的 JSON 数组：

    [
  {
    "fieldA": "value",
    "fieldB": "value"
  },
  ...
]

    不幸的是，MySQL 不允许选择所有带有*. 这会很棒，但不起作用：

    SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) FROM table;
    • 8

  4. 这将为您提供相同的答案，但代码更简单。我只是用json_objectwithgroup_concat来简化另一个答案。

    select 
    concat('[',
        GROUP_CONCAT(
            JSON_OBJECT(    
                'name_field', name_field
                ,'address_field', address_field
                ,'contact_age', contact_age
                )
            SEPARATOR ',')
    ,']')
    from contact;
    • 5

  5. 如果您不想在任何特定主机环境（例如 node.js、Python、MySQL Workbench）的上下文中执行此操作，那么MySQL Shell支持JSON 输出。结果中的每一行都作为单独的对象输出。它可以打印输出，并且可以输出对象数组或用换行符分隔每一行。例如，在 shell 提示符下，键入：

    \option resultFormat json/array

    然后正常运行查询。

    SELECT * FROM contacts;

    作为奖励，MySQL Shell 还支持 JavaScript 和 Python，但如果服务器设置为文档存储，您只能摆脱 SQL 语句。然后，一旦您设置了活动模式，您就可以通过db对象访问关系表。不管是使用JS还是Python，上面SQL查询对应的语句都是一样的：

    \use people
db.contacts.find()
    • 0

相关问题