mysql JSON_SET or JSON_INSERT function to insert object into nested object if it exists

Question

I have a json type column config I want to add a key value pair into, ultimately looking like:

{
  "features": {
    "divisions": true,
    "utilities": {
        "water": true,
        "gas": true,
        "electric": true
    }
  }
}

The issue I'm running into is when I want to insert the utilities object inside of features, I either overwrite the divisions key value or I'm returning NULL and the utilities object isn't being inserted.

Furthermore, the config column may be NULL or initially just be an empty {}.

This query will check for NULL or empty {} and also whether the features key exists but results in overwriting features if it already exists:

UPDATE entities SET config = JSON_SET(COALESCE(config, '{}'),
  COALESCE("$.features", "features"), JSON_OBJECT("utilities", 
  JSON_OBJECT("water", TRUE, "gas", TRUE, "electric", TRUE))) 
  WHERE id = 123725082;

This works fine unless the column already contains something like:

{
  "features": {
    "divisions": true,
  }
}

in which it overwrites divisions with the utilities object.

So I'm trying a JSON_INSERT query; from what I've gathered from mysql json functions documentation should work but it's returning null and I can't understand why:

UPDATE entities SET config = JSON_INSERT(COALESCE(config, '{}'),
  COALESCE("$.features", "features"), JSON_OBJECT("utilities", 
  JSON_OBJECT("water", TRUE, "gas", TRUE, "electric", TRUE))) 
  WHERE id = 123725082;

What's the point of COALESCE("$.features", "features")? It's the same as just "$.features". — Barmar
– Barmar, Commented Oct 31, 2017 at 20:33
I'm having trouble coming up with the full expression, but I think you need a mix of JSON_EXTRACT and COALESCE calls to handle missing properties at each level of the JSON structure. — Barmar
– Barmar, Commented Oct 31, 2017 at 20:43

wchiquito · Accepted Answer · 2017-11-01 08:43:13Z

6

JSON_MERGE function can be useful in this case.

Modify the UPDATE as needed:

UPDATE `entities`
  SET `config` = COALESCE(
    JSON_MERGE(
      `config`,
      JSON_OBJECT('features',
        JSON_OBJECT('utilities',
          JSON_OBJECT('water', TRUE, 'gas', TRUE, 'electric', TRUE)
        )
      )
    ),
    JSON_INSERT(
      JSON_OBJECT(),
      '$.features',
       JSON_OBJECT('utilities',
         JSON_OBJECT('water', TRUE, 'gas', TRUE, 'electric', TRUE)
       )
    )
  );

See db-fiddle.

edited Nov 1, 2017 at 8:43

answered Nov 1, 2017 at 7:55

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

1 Comment

ii iml0sto1 Over a year ago

The fiddle helped me out quite a bit to figure out these methods, thanks :)

Collectives™ on Stack Overflow

mysql JSON_SET or JSON_INSERT function to insert object into nested object if it exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related