mysql 使用 json_extract 解析 [{"AUFNR":"","BKTXT":"MK123040164_15456_陈沁","BLART":"ZB","BLDAT":"2023-04-28","BSCHL":"40","BUDAT":"2023-05-05","BUKRS":"2010","DMBTR":"100000.00","HKONT":"","KOSTL":"201L007","KURSF":"1","MONAT":"2023-05-05","MWSKZ":"J0","POSN2":"","PRCTR":"","PROJK":"","RMVCT":"","RSTGR":"","SAKNR":"70030022","SGTXT":"A15456_陈沁_2023-04-28-2023-04-28_其他招待支出","UMSKZ":"","VALUT":"","VBEL2":"","WAERS":"CNY","WRBTR":"100000.00","XBLNR":"MK123040164","XREF1":"","XREF1_HD":"","ZFBDT":"","ZUONR":"","ZZFI01":"","ZZFI02":"","formType":"员工其他费用报销流程"}]

您想要解析出这个 JSON 数组中的每个对象的“BKTXT”属性的值，可以使用以下 SQL 语句：

SELECT JSON_EXTRACT(your_json_column, '$[*].BKTXT') AS bktxt_values FROM your_table;

其中，`your_json_column` 是包含 JSON 数组的列名，`your_table` 是包含该列的表名。该语句将返回一个包含所有“BKTXT”属性的值的 JSON 数组。

相关问题

我运行出来的数据是这样的，怎么把乱码变成中文，[{"AUFNR":"","BKTXT":"MK123040164_15456_闄堟瞾","BLART":"ZB","BLDAT":"2023-04-28","BSCHL":"40","BUDAT":"2023-05-05","BUKRS":"2010","DMBTR":"100000.00","HKONT":"","KOSTL":"201L007","KURSF":"1","MONAT":"2023-05-05","MWSKZ":"J0","POSN2":"","PRCTR":"","PROJK":"","RMVCT":"","RSTGR":"","SAKNR":"70030022","SGTXT":"A15456_闄堟瞾_2023-04-28-2023-04-28_鍏朵粬鎷涘緟鏀嚭","UMSKZ":"","VALUT":"","VBEL2":"","WAERS":"CNY","WRBTR":"100000.00","XBLNR":"MK123040164","XREF1":"","XREF1_HD":"","ZFBDT":"","ZUONR":"","ZZFI01":"","ZZFI02":"","formType":"鍛樺伐鍏朵粬璐圭敤鎶ラ攢娴佺▼"}]

这是一个 JSON 格式的数据，其中包含了一些字段和对应的值。如果你想要将这些乱码变成中文，可以使用 JSON 解析器进行解析。在 Python 中，你可以使用 json 模块来解析 JSON 数据。例如：

import json

data = '[{"AUFNR":"","BKTXT":"MK123040164_15456_闄堟瞾","BLART":"ZB","BLDAT":"2023-04-28","BSCHL":"40","BUDAT":"2023-05-05","BUKRS":"2010","DMBTR":"100000.00","HKONT":"","KOSTL":"201L007","KURSF":"1","MONAT":"2023-05-05","MWSKZ":"J0","POSN2":"","PRCTR":"","PROJK":"","RMVCT":"","RSTGR":"","SAKNR":"70030022","SGTXT":"A15456_闄堟瞾_2023-04-28-2023-04-28_鍏朵粬鎷涘緟鏀嚭","UMSKZ":"","VALUT":"","VBEL2":"","WAERS":"CNY","WRBTR":"100000.00","XBLNR":"MK123040164","XREF1":"","XREF1_HD":"","ZFBDT":"","ZUONR":"","ZZFI01":"","ZZFI02":"","formType":"鍛樺伐鍏朵粬璐圭敤鎶ラ攢娴佺▼"}]'
parsed_data = json.loads(data)

print(parsed_data)

运行结果为：

[{'AUFNR': '', 'BKTXT': 'MK123040164_15456_闄堟瞾', 'BLART': 'ZB', 'BLDAT': '2023-04-28', 'BSCHL': '40', 'BUDAT': '2023-05-05', 'BUKRS': '2010', 'DMBTR': '100000.00', 'HKONT': '', 'KOSTL': '201L007', 'KURSF': '1', 'MONAT': '2023-05-05', 'MWSKZ': 'J0', 'P...

可以看到，使用 json.loads() 方法将 JSON 字符串解析成了 Python 中的字典类型，这样就可以方便地读取和处理数据了。

[{ "AUFNR": "", "BKTXT": "MK123050403_A23013_戴唯", "BLART": "ZB", "BLDAT": "2023-05-29", "BSCHL": "40", "BUDAT": "2023-05-30", "BUKRS": "2010", "DMBTR": "50.00", "HKONT": "", "KOSTL": "201L007", "KURSF": "1", "MONAT": "2023-05-30", "MWSKZ": "", "POSN2": "", "PRCTR": "", "PROJK": "", "RMVCT": "", "RSTGR": "", "SAKNR": "70030015", "SGTXT": "A15456_陈沁_2023-05-21-2023-05-21_高铁/火车票", "UMSKZ": "", "VALUT": "", "VBEL2": "", "WAERS": "CNY", "WRBTR": "50.00", "XBLNR": "MK123050403", "XREF1": "", "XREF1_HD": "", "ZFBDT": "", "ZUONR": "", "ZZFI01": "", "ZZFI02": "101001007003", "expenseCode": "FA25680423260527740", "formType": "员工其他费用报销流程" }, { "AUFNR": "", "BKTXT": "MK123050403_A23013_戴唯", "BLART": "ZB", "BLDAT": "2023-05-29", "BSCHL": "50", "BUDAT": "2023-05-30", "BUKRS": "2010", "DMBTR": "50.00", "HKONT": "", "KOSTL": "201L007", "KURSF": "1", "MONAT": "2023-05-30", "MWSKZ": "", "POSN2": "", "PRCTR": "", "PROJK": "", "RMVCT": "", "RSTGR": "", "SAKNR": "70030022", "SGTXT": "A15456_陈沁_2023-05-21-2023-05-21_高铁/火车票", "UMSKZ": "", "VALUT": "", "VBEL2": "", "WAERS": "CNY", "WRBTR": "50.00", "XBLNR": "MK123050403", "XREF1": "", "XREF1_HD": "", "ZFBDT": "", "ZUONR": "", "ZZFI01": "", "ZZFI02": "101001007003", "expenseCode": "FA25680423260527740", "formType": "员工其他费用报销流程" }]

根据提供的JSON内容，可以将其解析成如下的数据库表：

| 字段名 | 数据类型 | 说明 |
| ------ | ------- | ---- |
| AUFNR | VARCHAR(50) | |
| BKTXT | VARCHAR(100) | |
| BLART | VARCHAR(10) | |
| BLDAT | DATE | |
| BSCHL | VARCHAR(10) | |
| BUDAT | DATE | |
| BUKRS | VARCHAR(10) | |
| DMBTR | DECIMAL(18,2) | |
| HKONT | VARCHAR(50) | |
| KOSTL | VARCHAR(50) | |
| KURSF | DECIMAL(18,2) | |
| MONAT | DATE | |
| MWSKZ | VARCHAR(10) | |
| POSN2 | VARCHAR(50) | |
| PRCTR | VARCHAR(50) | |
| PROJK | VARCHAR(50) | |
| RMVCT | VARCHAR(50) | |
| RSTGR | VARCHAR(50) | |
| SAKNR | VARCHAR(50) | |
| SGTXT | VARCHAR(200) | |
| UMSKZ | VARCHAR(10) | |
| VALUT | VARCHAR(10) | |
| VBEL2 | VARCHAR(50) | |
| WAERS | VARCHAR(10) | |
| WRBTR | DECIMAL(18,2) | |
| XBLNR | VARCHAR(50) | |
| XREF1 | VARCHAR(50) | |
| XREF1_HD | VARCHAR(50) | |
| ZFBDT | DATE | |
| ZUONR | VARCHAR(50) | |
| ZZFI01 | VARCHAR(50) | |
| ZZFI02 | VARCHAR(50) | |
| expenseCode | VARCHAR(50) | |
| formType | VARCHAR(50) | |

对应的SQL语句为：

CREATE TABLE expense (
    AUFNR VARCHAR(50),
    BKTXT VARCHAR(100),
    BLART VARCHAR(10),
    BLDAT DATE,
    BSCHL VARCHAR(10),
    BUDAT DATE,
    BUKRS VARCHAR(10),
    DMBTR DECIMAL(18,2),
    HKONT VARCHAR(50),
    KOSTL VARCHAR(50),
    KURSF DECIMAL(18,2),
    MONAT DATE,
    MWSKZ VARCHAR(10),
    POSN2 VARCHAR(50),
    PRCTR VARCHAR(50),
    PROJK VARCHAR(50),
    RMVCT VARCHAR(50),
    RSTGR VARCHAR(50),
    SAKNR VARCHAR(50),
    SGTXT VARCHAR(200),
    UMSKZ VARCHAR(10),
    VALUT VARCHAR(10),
    VBEL2 VARCHAR(50),
    WAERS VARCHAR(10),
    WRBTR DECIMAL(18,2),
    XBLNR VARCHAR(50),
    XREF1 VARCHAR(50),
    XREF1_HD VARCHAR(50),
    ZFBDT DATE,
    ZUONR VARCHAR(50),
    ZZFI01 VARCHAR(50),
    ZZFI02 VARCHAR(50),
    expenseCode VARCHAR(50),
    formType VARCHAR(50)
);