从MEMSQL读取JSON [英] Reading JSON from MEMSQL

问题描述

JSON/MEMSQL有问题.这是我的桌子:

Having a problem with JSON / MEMSQL. Here's my table:

CREATE TABLE `MEMSQLPOLYGLOT`  ( 
    ID BIGINT AUTO_INCREMENT,
    `DATA`  JSON NULL,
    PRIMARY KEY (ID)
);

这是我要阅读的记录:

 insert into MEMTEST (DATA) values 
('
{
    "EnterpriseMessage" : 
        {
            "Body" :
            [
                {
                    "AccountNumber":"ABCD",
                    "AdminCompany":null,
                    "BrokerNumber":"WWonka"
                },
                {
                    "AccountNumber":"CSNE",
                    "AdminCompany":null,
                    "BrokerNumber":"ZWiza"
                }
            ],
        "Header" :
            {
                "mimetye":"application/vnd.ms-powerpoint",
                "destinationsystem":"ETL",
                "requiresack":"FALSE",
                "SimpArr": 
                    [
                        "BYTS6181",
                        "EVU98124",
                        "Genesys"
                    ],
                "EmptyFile":1
            }
    }

}
');

我可以在标题中读取SimpArr数组.

I can read the SimpArr array in the header.

SELECT DATA::EnterpriseMessage::Header::SimpArr from MEMTEST;

返回:

["BYTS6181","EVU98124","Genesys"]

我还可以传递键索引来获取特定值，例如:

I can also pass in a key index to get a specific value, such as:

select JSON_EXTRACT_JSON(DATA::EnterpriseMessage::Body, 1) from MEMTEST;

这是SimpArr的第二个值，因为它是从零开始的索引.

This returns the 2nd value of the SimpArr since it's a zero-based index.

我还可以读取主体中的对象数组:

I can also read the array of objects in the Body:

select JSON_EXTRACT_JSON(DATA::EnterpriseMessage::Body, 0) from MEMTEST;

返回该数组中的第一个对象:

which returns the first object in that array:

{
    "AccountNumber":"ABCD",
    "AdminCompany":null,
    "BrokerNumber":"WWonka"
}

但是，我无法找到一种方法来读取该对象的各个属性.

However, I am unable to find a way to read the individual attributes of this object.

有人有什么主意吗?

推荐答案

您可以将密钥作为附加参数传递给JSON_EXTRACT_JSON:

You can just pass the key as an extra argument to JSON_EXTRACT_JSON:

mysql> select JSON_EXTRACT_JSON(DATA::EnterpriseMessage::Body, 0, "AccountNumber") from MEMTEST;
+----------------------------------------------------------------------+
| JSON_EXTRACT_JSON(DATA::EnterpriseMessage::Body, 0, "AccountNumber") |
+----------------------------------------------------------------------+
| "ABCD"                                                               |
+----------------------------------------------------------------------+
1 row in set (0.08 sec)

JSON_EXTRACT_JSON接受任意数量的参数，可以是字符串键或整数数组索引.例如，我们可以在您上面的示例中展开::语法:

JSON_EXTRACT_JSON takes any number of arguments, either string keys or integer array indices. For example, we can unroll the :: syntax in your example above:

select JSON_EXTRACT_JSON(DATA, "EnterpriseMessage", "Body", 0, "AccountNumber") from MEMTEST;

还请注意，返回值中包含双引号.这是因为您提取了JSON，并且字符串的JSON表示形式带有双引号.如果您实际上想要获取字符串，请使用JSON_EXTRACT_STRING:

Also note that the return value has double quotes in it. That's because you extract JSON, and the JSON representation of a string has double quotes. If you actually want to get the string, use JSON_EXTRACT_STRING:

mysql> select JSON_EXTRACT_STRING(DATA, "EnterpriseMessage", "Body", 0, "AccountNumber") from MEMTEST;
+----------------------------------------------------------------------------+
| JSON_EXTRACT_STRING(DATA, "EnterpriseMessage", "Body", 0, "AccountNumber") |
+----------------------------------------------------------------------------+
| ABCD                                                                       |
+----------------------------------------------------------------------------+
1 row in set (0.07 sec)

这篇关于从MEMSQL读取JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！