MySQL json_remove returns null

Question

I have a table called product_option_param that has a column povs of JSON type, which basically keeps JSON arrays with ids (for example, [1,2,3])

When I need to delete, for example, an id 1, I execute this:

UPDATE product_option_param 
SET povs = JSON_REMOVE(
   povs, replace(JSON_SEARCH(povs, 'one', 1), '"', '')
) 
WHERE json_search(povs, 'one', 1) IS NOT NULL

But it just turns all cells that have 1 in the array into null. What am I doing wrong?

wchiquito · Accepted Answer · 2017-12-08 08:53:31Z

1

Try:

SET @`id` := '1';

UPDATE `product_option_param`
SET `povs` =
  JSON_REMOVE(
    `povs`,
    JSON_UNQUOTE(
      JSON_SEARCH(
        REPLACE(
          REPLACE(
            REPLACE(
              `povs`,
              '[',
              '["'
            ),
            ']',
            '"]'
          ),
          ',',
          '","'
        ),
      'one',
      @`id`
    )
  )
)
WHERE
  JSON_CONTAINS(`povs`,  @`id`);

See db-fiddle.

answered Dec 8, 2017 at 8:53

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL json_remove returns null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related