Mysql中JSON字段的值的實現(xiàn)示例

更新時間：2024年09月11日 09:20:56 作者：正常人誰寫代碼啊

本文中介紹了如何通過SQL語句查詢JSON字段中的特定數(shù)據(jù),如查詢數(shù)組中的元素,提取映射中的值,以及使用不同的JSON函數(shù)來處理數(shù)據(jù),感興趣的可以了解一下

我們在查詢mysql數(shù)據(jù)時，查詢某個字段的數(shù)劇是我們經(jīng)常接觸的，直接使用sql語句或者更方便的直接使用數(shù)據(jù)庫的orm語句查詢。但是如果需要查詢某個json字段里面的某些數(shù)據(jù)，orm模型可能都無法達到效果，還不如直接使用sql語句進行查詢來的直觀。下面總結了一些sql語句查詢json字段里面的值。

mysql版本是5.7，使用fastapi和tortoise-orm接口的方式返回查詢到的響應結果。

下面創(chuàng)建了一個用于測試的數(shù)據(jù)表。包括主鍵id，varchar類型的name，json類型的code（數(shù)組）和info（映射）。

例如：code數(shù)據(jù)結構：["A1b2C3d4E5", "F6g7H8i9J0", "K1l2M3n4O5", "P6q7R8s9T0", "U1v2W3x4Y5", "Z6a7B8c9D0", "E1F2g3H4i5", "J6k7L8m9N0", "O1P2q3R4s5", "T6U7v8W9x0", "Y1Z2a3B4c5", "D6E7F8g9H0", "I1j2K3l4M5", "N6O7P8q9R0", "S1T2U3v4W5", "X6Y7Z8a9B0"]
info數(shù)據(jù)結構：{"age": 30, "city": "New York", "name": "Alice", "contact": {"email": "alice@example.com", "phone": "123-456-7890"}, "education": "Bachelor"}

1、查詢info中age=30的數(shù)據(jù)

@router.get('/jsontest/{keyword}/{value}', description="獲取mysql的json值測試")
async def search_(keyword: str, value: str):
    query = f"SELECT * FROM jsontest WHERE JSON_CONTAINS(info->'$.{keyword}','{value}')"
    conn = tortoise.Tortoise.get_connection("default")
    try:
        _, index_result = await conn.execute_query(query)
    except Exception as ex:
        error_msg = f"error:{ex.__class__.__name__}-{str(ex)}"
        log_it(error_msg, level=logging.ERROR)
        return JSONResponse(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, content=error_msg)
    finally:
        await conn.close()
    return JSONResponse(
        status_code=status.HTTP_200_OK,
        content=index_result
    )

SELECT * FROM jsontest WHERE JSON_CONTAINS(info->'$.age','30')

查詢結果

為了避免重復代碼冗余，后續(xù)的查詢直接寫sql語句了?？梢酝ㄟ^更改api接口傳參，構造query語句達到一樣的效果。

2、查詢code數(shù)組中包含"ANOPQRSTU8"的數(shù)據(jù)

SELECT * FROM jsontest WHERE JSON_CONTAINS(code,'"ANOPQRSTU8"')

3、查詢info中city是New York并且code中包含AWXYZ01239的數(shù)據(jù)

SELECT * FROM jsontest WHERE JSON_CONTAINS(info->'$.city','"New York"') AND JSON_CONTAINS(code,'"AWXYZ01239"')

4、查詢info中包含city和age的數(shù)據(jù)，指定的是"one"表示只需包含任何一個路徑即可，"all"表示需要包含所有指定路徑

SELECT * FROM jsontest WHERE JSON_CONTAINS_PATH(info, 'one', '$.city', '$.age');
SELECT * FROM jsontest WHERE JSON_CONTAINS_PATH(info, 'all', '$.city', '$.contact.email');

5、查詢Alice info數(shù)據(jù)中的city，age，以及contact里面的email。下面兩種效果是一樣的，只不過使用JSON_EXTRACT返回的是一個字段，而->這種方法返回的是拆分開的字段

SELECT JSON_EXTRACT(info, '$.city','$.age','$.contact.email') AS name FROM jsontest WHERE name = 'Alice';
SELECT info->'$.city',info->'$.age',info->'$.contact.email' FROM jsontest WHERE name = 'Alice'

6、查詢Alice code數(shù)組中前三個數(shù)據(jù)。數(shù)組類型的json只能通過索引獲取值，如果想獲取全部則改成'$[*]'即可。下面兩種效果是一樣的，只不過使用JSON_EXTRACT返回的是一個字段，而->這種方法返回的是拆分開的字段

SELECT JSON_EXTRACT(code, '$[0]','$[1]','$[2]') AS res FROM jsontest WHERE name = 'Alice';
SELECT code->'$[0]',code->'$[1]',code->'$[2]' FROM jsontest WHERE name = 'Alice';
# 獲取數(shù)組里面的所有數(shù)據(jù)
SELECT JSON_EXTRACT(code, '$[*]') AS res FROM jsontest WHERE name = 'Alice';
SELECT code->'$[*]'  FROM jsontest WHERE name = 'Alice';

7、使用JSON_UNQUOTE去除 JSON 字符串的引號。上面返回的數(shù)據(jù)帶有原始json的引號，這一點有時對結果處理特別不友好，可以使用JSON_UNQUOTE進行處理

SELECT JSON_UNQUOTE(JSON_EXTRACT(info, '$.contact.email')) AS email FROM jsontest WHERE name = 'Alice';

8、提取info映射里面的所有key，也可以查詢嵌套字典里面的所有key

SELECT JSON_KEYS(info) AS k FROM jsontest WHERE name = 'Alice';
#查詢嵌套字典的key
SELECT JSON_KEYS(info->'$.contact') AS k FROM jsontest WHERE name = 'Alice';

9、獲取code數(shù)組和字典info的長度

SELECT JSON_LENGTH(code, '$') as count  FROM jsontest WHERE name = 'Alice'
SELECT JSON_LENGTH(info, '$') as count  FROM jsontest WHERE name = 'Alice'
# 獲取嵌套字典的長度
SELECT JSON_LENGTH(info->'$.contact') as count  FROM jsontest WHERE name = 'Alice'

10、搜索數(shù)組和字典里面的值

# 搜索字典中的value，one_or_all: 指定搜索所有匹配項還是僅找到的第一個匹配項
SELECT JSON_SEARCH(info, 'all', "New York") AS search_result FROM jsontest
# 搜索數(shù)組中的值，%A%模糊搜索含有A的數(shù)據(jù)
SELECT JSON_SEARCH(code, 'all', '%A%') AS search_result FROM jsontest

到此這篇關于Mysql中JSON字段的值的實現(xiàn)示例的文章就介紹到這了,更多相關Mysql JSON字段值內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: