CODEWITHSUNDEEP
mysqljsonselect
user3058763
user3058763

Reputation: 569

MySQL 5.7.22: How to SELECT JSON value when it's key contains dot character?

So, the JSON structure looks like that:

{
  "pixel.uuid": "1ca86b7d7505c48363b2a5d9dde0c7e4",
  "innerWidth": "1440"
}

Question: How do I select "pixel.uuid"? I tried these combinations:

select json->"$.pixel.uuid" from TrackingData_experimental

 

select json->"$.pixel\.uuid" from TrackingData_experimental

 

select json->{"$.pixel.uuid"} from TrackingData_experimental

  

select JSON_EXTRACT(json, '$.pixel.uuid') from TrackingData_experimental

But with no luck. Thank you!

Upvotes: 1

Views: 651

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562408

Delimit the key in double-quotes:

mysql> select json->'$."pixel.uuid"' from TrackingData_experimental;
+------------------------------------+
| json->'$."pixel.uuid"'             |
+------------------------------------+
| "1ca86b7d7505c48363b2a5d9dde0c7e4" |
+------------------------------------+

If you want the value returned unquoted, do this:

mysql> select json->>'$."pixel.uuid"' from TrackingData_experimental;
+----------------------------------+
| json->>'$."pixel.uuid"'          |
+----------------------------------+
| 1ca86b7d7505c48363b2a5d9dde0c7e4 |
+----------------------------------+

Upvotes: 1

Related Questions