1

What I WANT to do, but I guess it's somewhow not possible:

I have JSON data in a column for saving translations. for example column description value: {"de": "hi wie gehts?", "en": "hi, how are you?"}

Now I want to update only the "en" property. I know it's possible with JSON_REPLACE but it has a weird syntax where I have to generate SQL code first to make this happen.

What I am looking for is the possibility to just send {"en": "hi, how are you!?"} and then it should keep the "de" property and only updating the "en" property.

There is the JSON_MERGE function, but this does not what I am looking for, but apart from that, that's the syntax I like to use.

Is there a workaround with some nested mysql functions or so?

Improve this question
4
  • Try: JSON_SET(), see db-fiddle. Commented Aug 24, 2017 at 15:59
  • Thanks, but I know this functions. It's not what I want to do. I want to send plain JSON. Commented Aug 24, 2017 at 19:07
  • 1
    With the JSON functions available, it is not easy to achieve what you need. If the plain JSON always has a single key, you can try something like db-fiddle, if you have more than one key in your plain JSON, you will need something more complex to achieve your goal. Commented Aug 25, 2017 at 9:11
  • Dude wtf, you are my hero :) ... I just "solved" this by having the same idea with first removing and then merging, but I generated the remove function string in backend code and not directly with myql. Your solution is one level better. Thank you so much! If you like to get the reputation for this answer, feel free to add an answer and I will accept it. Otherwise I will create the answer to make the solution better visible to others. Commented Aug 25, 2017 at 9:31

1 Answer 1

Reset to default
1

With the JSON Functions available, it is not easy to achieve what you need. If the plain JSON always has a single key, you can try something like the following (tested on MySQL 5.7.19):

SET
  @`json` := '{"de": "hi wie gehts?", "en": "hi, how are you?"}',
  @`plain_json` := '{"en": "hi, how are you!?"}';

SELECT
  JSON_MERGE(
    JSON_REMOVE(
      @`json`,
      CONCAT('$.',
             JSON_EXTRACT(
               JSON_KEYS(@`plain_json`),
               '$[0]'
            )
      )
    ),
    @`plain_json`
  ) AS `update`;

This results in:

+----------------------------------------------------+
| update                                             |
+----------------------------------------------------+
| {"de": "hi wie gehts?", "en": "hi, how are you!?"} |
+----------------------------------------------------+

If you have more than one key in your plain JSON, you will need something more complex to achieve your goal.

See db-fiddle.

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

4 Comments

Can you think of any version that works recursively? For instance, if '$.en.greeting' needed to be replaced, instead of '$.en'.
@piojo: I don't fully understand your question, something like db-fiddle?.
I mean a function which will merge, but when a key exists in both the parent and child and the value is not an object/array, the 2nd value will be taken. Like JSON_MERGE_PATCH from MySQL or jsonb_recursive_merge from this answer. The function you linked creates an array for inputs @json := '{"A1": {"B1": "OLD DATA 2"}}' and @plain_json := '{"A2": "new data 1", "A1": {"B1": "new data 2"}}', though it works for some other cases. (Note that I've already switched to MySQL, but this seems an important issue in MariaDB.)
@piojo: In case that MariaDB does not include the functions JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE() of MySQL, it will be necessary to do something more complex to obtain the same behavior.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.