MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS `orders`;
CREATE TABLE IF NOT EXISTS `orders` (
  `id` SERIAL,
  `items` TEXT
);
INSERT INTO `orders`
  (`items`)
VALUES ('
  {
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "140",
      "amount": "4",
      "category": "9"
    },
    "24": {
      "name": "item 2",
      "step": "1",
      "price": "6.2",
      "amount": "1",
      "category": "5"
    },
    "35": {
      "name": "item 3",
      "step": "1",
      "price": "2.9",
      "amount": "3",
      "category": "1"
    },
    "37": {
      "name": "item 4",
      "step": "1",
      "price": "3.9",
      "amount": "2",
      "category": "9"
    }
  }'),
  ('{
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "141",
      "amount": "4",
      "category": "9"
    }
  }'),
  ('{
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "141",
      "amount": "4",
      "category": "8"
    }
  }');
SELECT `id`, `items`
FROM `orders`;


id
items


1

  {
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "140",
      "amount": "4",
      "category": "9"
    },
    "24": {
      "name": "item 2",
      "step": "1",
      "price": "6.2",
      "amount": "1",
      "category": "5"
    },
    "35": {
      "name": "item 3",
      "step": "1",
      "price": "2.9",
      "amount": "3",
      "category": "1"
    },
    "37": {
      "name": "item 4",
      "step": "1",
      "price": "3.9",
      "amount": "2",
      "category": "9"
    }
  }

2
{
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "141",
      "amount": "4",
      "category": "9"
    }
  }

3
{
    "10": {
      "name": "item 1",
      "step": "1",
      "price": "141",
      "amount": "4",
      "category": "8"
    }
  }


SET @`category` := '9';
SELECT
GROUP_CONCAT('
SELECT
  `id`,
  JSON_EXTRACT(CONCAT(\'[\', REPLACE(JSON_COMPACT(`items`), \'"},"\', \'"}},{"\'), \']\'), ',
        REGEXP_REPLACE(
          JSON_SEARCH(
            CONCAT('[', REPLACE(JSON_COMPACT(`items`), '"},"', '"}},{"'), ']'),
            'all',
            @`category`,
            NULL,
            '$**.category'
          )
        , '^\\[|\\.\\d+\\.category|\\]$', ''), '
  ) `items`
FROM `orders`
WHERE `id` = ', `id`
  SEPARATOR ' UNION ALL ') INTO @`sql`
FROM
  `orders`
WHERE
JSON_CONTAINS(
  JSON_EXTRACT(`items`,
  '$**.category'),
  CONCAT('"', @`category`, '"')
);
PREPARE `stmt` FROM @`sql`;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

EXECUTE `stmt`;
Unknown prepared statement handler (stmt) given to EXECUTE

DEALLOCATE PREPARE `stmt`;
Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE

id	items
1	{ "10": { "name": "item 1", "step": "1", "price": "140", "amount": "4", "category": "9" }, "24": { "name": "item 2", "step": "1", "price": "6.2", "amount": "1", "category": "5" }, "35": { "name": "item 3", "step": "1", "price": "2.9", "amount": "3", "category": "1" }, "37": { "name": "item 4", "step": "1", "price": "3.9", "amount": "2", "category": "9" } }
2	{ "10": { "name": "item 1", "step": "1", "price": "141", "amount": "4", "category": "9" } }
3	{ "10": { "name": "item 1", "step": "1", "price": "141", "amount": "4", "category": "8" } }