CODEWITHSUNDEEP
jsondatabasemariadbnosql
BeGo
BeGo

Reputation: 175

List in a nested json in mariaDB with json_table

I can't get the data from a nested json using json_table My MariaDB server is 10.6.0

set @json='
{
   "id":"9696e1ac-e173-4905-ac51-c47c0ef8c90b",
   "username":"[email protected]",
   "firstName":"DEMO",
   "lastName":"DEMO",
   "email":"[email protected]",
   "isEnabled":true,
   "attributes":{
      "ASSOCIATED_COMPANY":"CLCORP001,CLCORP002,CLCORP003,CLCORP004,PECORP001,PECORP002,PECORP003,PECORP004,PECORP005,DECORP001",
      "ROLES":"[\"Super Admin\",\"Admin\",\"Executive\"]",
   },
}';

I tried this but it doesn't work

SELECT
    *
FROM
    SELECT  * FROM json_table(@json, '$.*' COLUMNS(NESTED PATH '$.attributes.ROLES[*]'  COLUMNS (roles varchar(250) PATH '$'))) roles
TEXT PATH '$'))) roles

Upvotes: 0

Views: 143

Answers (1)

Georg Richter
Georg Richter

Reputation: 7476

No matter if MySQL or MariaDB: If your json string is not valid, json_object will always fail.

In both MySQL and MariaDB you can use json_valid() function to check your json:

set @json='
{
   "id":"9696e1ac-e173-4905-ac51-c47c0ef8c90b",
   "username":"[email protected]",
   "firstName":"DEMO",
   "lastName":"DEMO",
   "email":"[email protected]",
   "isEnabled":true,
   "attributes":{
      "ASSOCIATED_COMPANY":"CLCORP001,CLCORP002,CLCORP003,CLCORP004,PECORP001,PECORP002,PECORP003,PECORP004,PECORP005,DECORP001",
      "ROLES":"[\"Super Admin\",\"Admin\",\"Executive\"]",
   },
}';

SELECT JSON_VALID(@json);
+-------------------+
| JSON_VALID(@json) |
+-------------------+
|                 0 |
+-------------------+

On success JSON_VALID will return 1.

I didn't check in detail, but looks like the extra quotation marks in attributes.ROLES are not allowed.

Upvotes: 1

Related Questions