为了账号安全,请及时绑定邮箱和手机立即绑定

针对嵌套 JSON 字段、MYSQL、PHP 的查询

针对嵌套 JSON 字段、MYSQL、PHP 的查询

PHP
杨__羊羊 2023-12-15 15:47:59
我有这样的结构:{"placards":    [    {"barcode": "string", "destination":"string", "weight":"string"},    {etc...}]}存储在 MYSQL 数据库中的单个列上。我正在尝试搜索特定的条形码,并返回该条形码位于该行的 json 结构内的整行。我尝试了两种方法,两种方法都在堆栈溢出中找到,但我没有在一种方法中得到结果,下面我将发布后者的错误。attempt1:"SELECT * FROM table WHERE placards[*->barcode] = ".$job->events[0]->imcb;attempt2:    $sampleBC = $job->events[0]->imcb;        $sql = 'SELECT * FROM(        SELECT data_column->"[*]" as row        from table        where $sampleBC IN JSON_EXTRACT(data_column, ""    )    WHERE row->".barcode" = $sampleBC';这些方法都不会在 $stmt->error 上给出错误,但我实际上无法通过此查询成功获取任何内容。
查看完整描述

2 回答

?
蝴蝶刀刀

TA贡献1801条经验 获得超8个赞

您只需使用json_contains()

select *

from mytable

where json_contains(data_column, '{ "barcode": "foo" }' , '$.placards')

此短语为:搜索路径 'placards' 下数组中包含候选对象的键/值对的任何元素'{ "barcode": "foo" }',其中 'foo' 是您搜索的条形码值。

DB Fiddle 演示

set @data_column = 

    '{

        "placards": [

            {"barcode": "foo", "destination":"string", "weight":"string"},

            {"barcode": "bar", "destination":"string", "weight":"string"}

        ]

    }';


select json_contains(@data_column, '{ "barcode": "foo" }' , '$.placards') is_a_match;

| is_a_match |

| ---------: |

|          1 |


select json_contains(@data_column, '{ "barcode": "baz" }' , '$.placards') is_a_match;


| is_a_match |

| ---------: |

|          0 |

从 MySQL 8.0.17 开始,你甚至可以在嵌套的 json 数组上创建多值索引,这样数据库就不需要为此查询执行全表扫描:


alter table mytable 

    add index myidx( (cast(data_column -> '$.placards' as unsigned array)) );


查看完整回答
反对 回复 2023-12-15
?
摇曳的蔷薇

TA贡献1793条经验 获得超6个赞

您必须使用 JSON_TABLE():


SELECT mytable.* FROM mytable,

  JSON_TABLE(mytable.data_column, '$.placards[*]' COLUMNS (

    barcode VARCHAR(100) PATH '$.barcode',

    destination VARCHAR(100) PATH '$.destination', 

    weight VARCHAR(20) PATH '$.weight'

  )) AS j

WHERE j.barcode = ?

如果将这些字段存储在普通列中而不是 JSON 中,则会简单得多。每个标语牌存储一行,并包含 barcode、description 和 weight 的单独列。


SELECT m.* FROM mytable AS m JOIN placards AS p ON m.id = p.mytableid 

WHERE p.barcode = ?

我在 Stack Overflow 问题中看到的 MySQL 中 JSON 的大多数使用都是不必要的,因为数据不需要 JSON 的灵活性。针对 JSON 文档的查询很难编写,也很难优化。 JSON 还需要更多空间来存储相同的数据。


查看完整回答
反对 回复 2023-12-15
  • 2 回答
  • 0 关注
  • 109 浏览

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信