CODEWITHSUNDEEP
mysqljson
Rajesh Iyer
Rajesh Iyer

Reputation: 1

Mysql JSON_EXTRACT with double quotes in path not working

I have the following table def:

`CREATE TABLE `TestInfo` (
  `Info` json DEFAULT NULL
) ;
`

Am inserting two rows with json values.

INSERT INTO `TestInfo` (`Info`)
VALUES
    ('{
  "statusCode": 200,
  "result": {
    "summary": {
      "area": 0.0009904206008286565
    }
    }
} '
);

INSERT INTO `TestInfo` (`Info`)
VALUES
    (
'{
  "statusCode": 200,
  "result": {
    "summary": {
      "area": 0.0009904206008286565,
      "realty-society": {
        "price-min": {
          "property": "price-min",
          "min": 110000.00000000001,
          "max": 150000000,
          "average": 31184468.085106384,
          "sum": 1465670000
        }
      }
    }
  }
} '
);

When I run the query:

SELECT JSON_EXTRACT(Info, '$.result.summary') 
FROM TestInfo ;

it returns the 2 rows. This is fine. But when I run the same query with double quotes around the path like this:

SELECT JSON_EXTRACT(Info, '$."result.summary"') 
FROM TestInfo;

it returns the 2 rows (with the single column) as NULLs.

Ultimately I need to use double quotes for keys that have a hyphen (dash) in them. I am using MySQL 5.7 on AWS. Pl help.

Upvotes: 0

Views: 4312

Answers (2)

Roshan Perera
Roshan Perera

Reputation: 786

This should be works if MYSQL version>=5.7

SELECT Info->>"$.result.summary" 
FROM TestInfo ;

Upvotes: 1

Barmar
Barmar

Reputation: 780974

Don't put double quotes around the whole path, just around a specific property name that contains special characters, e.g.

SELECT JSON_EXTRACT(Info, '$.result.summary."realty-society"."price-min"')
FROM TestInfo

Yuor code makes . part of the literal property name, rather than a separator between properties. You would use it if you had:

"result.summary": ...

in the object.

Upvotes: 1

Related Questions