JSON_EXTRACT is returning an array - how to get the value-CodePudding

I have the following to extract the userId from the Json field:

SELECT JSON_EXTRACT(userData, '$**.UserId') as UserId FROM userTable;

is returning [26933] but I am after just 26933.

An example Json record in the userData field:

{
   "users":{
      "user": {
            "UserId": 26933,
            "FirstName": "John",
            "LastName": "Smith"         
        }
    }
}

Have tried '$**[0].UserId' but also returns the array and not the value.

SELECT JSON_EXTRACT(userData, '$**.FirstName') as UserId FROM userTable;

returns

["John"]

How do I remove the brackets and quotes?

SELECT userData->>'$**.FirstName' as UserId FROM userTable;

but the same result.

CodePudding user response：

First Of All Your Json stored in the Table has an error , Remove the comma after "LastName": "Smith"

here is the corrected json

{
   "users":{
      "user": {
            "UserId": 26933,
            "FirstName": "John",
            "LastName": "Smith"            
        }
    }
}

And To the answer of your question you can use the below

SELECT JSON_UNQUOTE(JSON_EXTRACT(userData, '$.users.user.FirstName')) AS UserId from userTable