mysql - 无法将 json_extract 与多嵌套的 json 字符串一起使用到 MySQL 表中

Question

multi-nested-json-object screenshot

我在 MySQL 的一个表中有 json 数据，它有一个对象嵌套在一个对象(由 Wordpress 创建)中，我需要获取 oid 和 product_id(见屏幕截图)。我的问题是对象“eBuefzeXhMBTVWFyF”是一个从 wordpress 插件自动生成的字符串，有助于列出放弃购物车的用户。

想象一下 MySQL 中只有一个列的表，看起来像这样...... screenshot

column_name: abandoned_cart
row1: {"cart":{"eBuefzeXhMBTVWFyF":{"oid":"tUWTDArmqojehvsGc","key":"eBuefzeXhMBTVWFyF","product_id":48337,"variation_id":0,"variation":[],"quantity":1,"data_hash":"BzcZvMnrKFUZtBftM","line_tax_data":{"subtotal":[],"total":[]},"line_subtotal":88,"line_subtotal_tax":0,"line_total":88,"line_tax":0}}}
row2: {"cart":{"AkLwfKZzyUwCcvvqV":{"oid":"XrsAwnqxspvqAvYGv","key":"AkLwfKZzyUwCcvvqV","product_id":7453,"variation_id":0,"variation":[],"quantity":1,"data_hash":"RoUNjJxfhqeveEHJW","line_tax_data":{"subtotal":[],"total":[]},"line_subtotal":25,"line_subtotal_tax":0,"line_total":25,"line_tax":0}}}

我可以使用 json_extract 通过以下查询获取第一个对象 ... screenshot

select
json_extract(abandoned_cart_info, '$.cart') cart
from json.abandoned_cart;

我的结果看起来像这样……这是正确的……screenshot

column_name: cart
row1: {"eBuefzeXhMBTVWFyF":{"oid":"tUWTDArmqojehvsGc","key":"eBuefzeXhMBTVWFyF","product_id":48337,"variation_id":0,"variation":[],"quantity":1,"data_hash":"BzcZvMnrKFUZtBftM","line_tax_data":{"subtotal":[],"total":[]},"line_subtotal":88,"line_subtotal_tax":0,"line_total":88,"line_tax":0}}
row2: {"AkLwfKZzyUwCcvvqV":{"oid":"XrsAwnqxspvqAvYGv","key":"AkLwfKZzyUwCcvvqV","product_id":7453,"variation_id":0,"variation":[],"quantity":1,"data_hash":"RoUNjJxfhqeveEHJW","line_tax_data":{"subtotal":[],"total":[]},"line_subtotal":25,"line_subtotal_tax":0,"line_total":25,"line_tax":0}}

我的问题是如何将 json_extract 用于自动生成的 key (例如 eBuefzeXhMBTVWFyF 和 AkLwfKZzyUwCcvvqV 等等)来获取 oid 和 product_id？

select
    json_extract(abandoned_cart_info, '$.eBuefzeXhMBTVWFyF.oid') oid,
    json_extract(abandoned_cart_info, '$.eBuefzeXhMBTVWFyF.product_id') product_id,
    json_extract(abandoned_cart_info, '$.AkLwfKZzyUwCcvvqV.oid') oid,
    json_extract(abandoned_cart_info, '$.AkLwfKZzyUwCcvvqV.product_id') product_id,

from json.abandoned_cart;

上面的查询显然是不够的，因为 key 是自动生成的。我缺少什么来完成这项工作？

Answer 1

您可以使用以下解决方案使用 JSON_EXTRACT :

SELECT
  JSON_UNQUOTE(REGEXP_REPLACE(JSON_KEYS(JSON_EXTRACT(abandoned_cart_info, '$.cart')), '^\\[|\\]$', '')) AS obj_key,
  JSON_UNQUOTE(REGEXP_REPLACE(JSON_EXTRACT(abandoned_cart_info, '$.cart.*.oid'), '^\\[|\\]$', '')) AS oid,
  JSON_UNQUOTE(REGEXP_REPLACE(JSON_EXTRACT(abandoned_cart_info, '$.cart.*.product_id'), '^\\[|\\]$', '')) AS product_id
FROM abandoned_cart;

... 或使用 ->>:

SELECT
  JSON_UNQUOTE(REGEXP_REPLACE(JSON_KEYS(abandoned_cart_info->>'$.cart'), '^\\[|\\]$', '')) AS obj_key,
  JSON_UNQUOTE(REGEXP_REPLACE(abandoned_cart_info->>'$.cart.*.oid', '^\\[|\\]$', '')) AS oid,
  JSON_UNQUOTE(REGEXP_REPLACE(abandoned_cart_info->>'$.cart.*.product_id', '^\\[|\\]$', '')) AS product_id
FROM abandoned_cart;

demo on dbfiddle.uk