1

So the main problem I'm having is that I really need to get a value from another table that is in JSON format, obviously I thought, well just use JSON_VALUE to get the property I want... Problem arises when I created in adonis the migration, and surprise. After running the migrations I realise I can't use JSON_VALUE because of the version of adonis server. Is there another way I can get the value?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Josué Arreola
  • 41
  • 6
  • Can't you upgrade the MySQL installation on your server? – Tangentially Perpendicular Apr 21 '21 at 02:23
  • Fetch the whole JSON document into your app, unmarshal it into a struct, and pick out the value you want. You will regret any other solution. I do recommend you upgrade your MySQL version. If you're on MySQL 5.6 or earlier, [it's already past its end of life.](https://endoflife.software/applications/databases/mysql) – Bill Karwin Apr 21 '21 at 02:58
  • @BillKarwin *If you're on MySQL 5.6 or earlier* JSON_VALUE() was introduced in 8.0.21. – Akina Apr 21 '21 at 05:41
  • *Is there another way I can get the value?* There is a lot of string functions, it is enough for to solve your task. I'd recommend to create universal user-defined function, but in each separate case you may build separate specific expression, of course. Cast to needed output datatype (ex- or implicit) must be applied in the query anycase - function cannot have changeable output datatype. – Akina Apr 21 '21 at 05:44
  • Ah, right, I'm thinking of other functions in 5.7 like JSON_SEARCH(). – Bill Karwin Apr 21 '21 at 06:43

1 Answers1

0

Well, it was easier than I'd expected. I could, because I wanted to search a String inside the JSON it was as easy as using LIKE. Example

user.settings was a JSON more less like: '{"platform" : "windows"}'

UPDATE order
      SET order.platform = (
        SELECT
          CASE 
            WHEN users.settings LIKE '%mac%' THEN 'mac'
            WHEN users.settings LIKE '%windows%' THEN 'windows'
            ELSE NULL
          END
        FROM users
        WHERE users.id = orders.user_id
      )
Josué Arreola
  • 41
  • 6