0

I have a SQL variable which stores the following JSON object:

{"name": "Dan", "country": "Japan", "job": "manager"}

My goal is to sort the object based on the keys alphabetically and return it {"country": "Japan", "job": "manager", "name": "Dan"}. The keys will vary for different json objects.

Unfortunately, I am unable to use OpenJson because of compatibility issue and it may stay that way. So I tried using JSON_TABLE command. My overall goal is to:

  1. Create a table with two columns, keys and values, keys will hold all the keys in the json object, whereas values will hold the corresponding value for the key in that row.
  2. Use order by key to return the result in sorted way
  3. Return the result in a json object format

However, I am getting stuck at the first step. I created two variables to store the json arrays for the keys and values separately using JSON_KEYS and JSON_EXTRACT. Then, I combined them into another variable, which has the following structure: {key: ['name', 'country', 'job'], value:['Dan', 'Japan', 'manager']}. Finally, I used JSON_Table on it with the hope of completing step 1. But since they are sibling arrays, JSON_TABLE produced two columns where total number of rows are 6 instead of 3. I do not know how to get past this.

Is there any simpler solution than this?

Thanks in advance.

Rudra Saha
  • 1
  • 1
  • 1
    Why do you want to create a table with two columns when you have three keys? It would be much easier if you would use the initial JSON object as input for the JSON_TABLE. – Reto Jul 13 '21 at 07:00
  • I created a table with two columns because, I want all the key names to appear as row so that later I can apply the order by clause. I am not sure how to use the initial object as input for json_table, since it does not have a uniform structure. for ex: if the json object was like this: [{'key': 'name', 'value':'Dan'},{'key': 'country', 'value':'Japan'}], then it would probably be easier to apply json_table? – Rudra Saha Jul 13 '21 at 08:04
  • Have a look @ the [documentation](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html), I think you're making your life unnecessary complicated ;) – Reto Jul 13 '21 at 17:06

1 Answers1

1

This is because of you set type JSON of column. So it converts automatically in alphabetically order. If you want to save data of json array as you insert(not rearrange in alphabetically). you need to change column type JSON to TEXT. it will work.

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M Sohaib
  • 21
  • 3
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – jasie Sep 20 '22 at 09:47