将 SQL 查询导出到 JSON 格式的文本文件

问题描述

我需要一种方法来运行 SQL 命令，然后将结果导出到 JSON 格式的文本文件.

I need a way to run a SQL command and then export the results to a JSON formatted text file.

我有这个链接:https:///falseisnotnull.wordpress.com/2014/11/23/creating-json-documents-with-mariadb/

但我不理解他声明中的 CREATE_COLUMN 部分，也不真正理解他用来理解它与我的数据库的关系的术语.

But I don't understand the CREATE_COLUMN section of his statement, nor really the terminology he uses to understand how it relates to my DB.

任何人都可以在这样的查询中为我简化他的示例吗?

Can anyone please simplify his example for me on a query like this?

SELECT * FROM thisismy.database;

如果我使用 INTO OUTFILE 命令执行上述操作，我会得到如下所示的数据:

If I do the above with the INTO OUTFILE command, I get data that looks like this:

1     Armand         Warren   56045   Taiwan, Province of China   0        0
2      Xenos    Salas        71090        Liberia        0                0
3      Virginia   Whitaker   62723   Nicaragua   0                       0
4      Kato Patrick   97662   Palau   0                       0
5      Cameron      Ortiz P9C5B6   Eritrea 0                0

但我需要它看起来像这样:

But I need it to look like this:

{ "aaData": [
    [ "1", "Armand", "Warren", "56045", "Taiwan, Province of China" ],
    [ "2", "Xenos", "Salas", "71090", "Liberia" ],
    [ "3", "Virginia", "Whitaker", "62723", "Nicaragua" ],
    [ "4", "Kato", "Patrick", "97662", "Palau" ],
    [ "5", "Cameron", "Ortiz", "P9C 5B6", "Eritrea" ]
] }

有什么建议吗?

谢谢

如果有帮助，我会运行 MariaDB

I run MariaDB if that helps

推荐答案

SELECT CONCAT('[
	', GROUP_CONCAT(
        COLUMN_JSON(
            COLUMN_ADD(
                COLUMN_CREATE('id', id)
                , 'name', name
                , 'price', price
            )
        )
        ORDER BY id
        SEPARATOR ',
	'
    ), '
]') AS json
    FROM product G

忽略除 COLUMN_CREATE 之外的所有内容.这是创建 JSON 的地方.好的，所以我们有:

Ignore everything except COLUMN_CREATE. This is where the JSON creation is happening. OK, so we have:

COLUMN_JSON(
        COLUMN_ADD(
            COLUMN_CREATE('id', id)
            , 'name', name
            , 'price', price
        )
    )

COLUMN_ADD 是将列添加到 JSON 的函数.每个参数都是与其值配对的键.所以 'name' 是 JSON 对象中的键，name 是值.在这种情况下，它是表 product 中的列 name.

COLUMN_ADD is the function that adds the columns to the JSON. Each argument is a key paired with its value. So 'name' is what the key in the JSON object will be and name is what the value will be. In this case it's the column name from the table product.

假设您要查询 users 表并获取他们的名字、姓氏和 ID.这就是您的查询的样子:

So, let's say you want to query your users table and get their first names, last names, and ID. This is what your query would look like:

SELECT CONCAT('[
	', GROUP_CONCAT(
        COLUMN_JSON(
            COLUMN_ADD(
                COLUMN_CREATE('id', id)
                , 'first_name', first_name
                , 'last_name', last_name
            )
        )
        ORDER BY id
        SEPARATOR ',
	'
    ), '
]') AS json
    FROM users G

在 COLUMN_JSON 命令的末尾，我们有 AS json，它将其转换为您想要的 JSON 类型.

And at the end of the COLUMN_JSON command we have AS json, which casts it as the JSON type you want.

这篇关于将 SQL 查询导出到 JSON 格式的文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！