MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.30


DROP TABLE IF EXISTS `packet_code`;
CREATE TABLE IF NOT EXISTS `packet_code` (
  `id` SERIAL,
  `goods` JSON
);
INSERT INTO `packet_code`
  (`id`, `goods`)
VALUES
  (582, '[
      {"code": "S87719300077661", "size": "7", "loged_by": "93,xx"}, 
      {"code": "S87719300094874", "size": "9", "loged_by": "93,xx"}, 
      {"code": "S87719300112648", "size": "11", "loged_by": "93,xx"}
    ]'
  ),
  (583, '[
      {"code": "S87719300077662", "size": "7", "loged_by": "93,xx"}, 
      {"code": "S87719300094875", "size": "9", "loged_by": "93,xx"}, 
      {"code": "S87719300112649", "size": "11", "loged_by": "93,xx"}
    ]'
  );

Records: 2  Duplicates: 0  Warnings: 0


SELECT `id`, JSON_PRETTY(`goods`)
FROM `packet_code`
WHERE JSON_SEARCH(
  `goods`,
  'all',
  'S87719300077661',
  NULL,
  '$[*].code') IS NOT NULL;


id
JSON_PRETTY(`goods`)


582
[
  {
    "code": "S87719300077661",
    "size": "7",
    "loged_by": "93,xx"
  },
  {
    "code": "S87719300094874",
    "size": "9",
    "loged_by": "93,xx"
  },
  {
    "code": "S87719300112648",
    "size": "11",
    "loged_by": "93,xx"
  }
]