Mysql JSON_EXTRACT with double quotes in path not working

Question

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.

Barmar · Accepted Answer · 2018-02-13 06:44:19Z

1

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.

edited Feb 13, 2018 at 6:44

answered Feb 13, 2018 at 6:28

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rajesh Iyer Over a year ago

Tried the query you suggested with the double quotes only around "price-min". I get 2 rows with NULLs in the result.

Rajesh Iyer Over a year ago

It worked. The right query is 'SELECT JSON_EXTRACT(Info, '$.result.summary."realty-society"."price-min"') FROM TestInfo;'. Thanks a lot for the tip. Makes perfect sense.

Roshan Perera · Accepted Answer · 2019-10-16 08:53:28Z

1

This should be works if MYSQL version>=5.7

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

answered Oct 16, 2019 at 8:53

Roshan Perera

7867 silver badges12 bronze badges

Collectives™ on Stack Overflow

Mysql JSON_EXTRACT with double quotes in path not working

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related