MySQL JSON Path with semicolon in element

Question

If I have a JSON doc like this:

{
  "foo": "bar",
  "foo:oo": "bar2"
}

I can access the property "foo" with:

SELECT col->"$.foo" // Outputs "bar"

However, if I try to access the property named "foo:oo" like so:

SELECT col->"$.foo:oo" // Should output "bar2", but triggers an error

I get the following error:

Invalid JSON path expression. The error is around character position 8.

How can I access a property that has a semicolon?

Paul · Accepted Answer · 2018-03-14 00:45:47Z

2

SELECT col->"$foo:oo" // Should output "bar2", but triggers an error

You are missing a dot:

SELECT col->"$.foo:oo"

I got this to work in an online mysql environment:

CREATE TABLE t1 (sentence JSON);
INSERT INTO t1 VALUES('{"foo": "bar", "foo:oo": "bar2"}');    
SELECT sentence ->> '$."foo:oo"' FROM t1;

edited Mar 14, 2018 at 0:45

answered Mar 13, 2018 at 23:48

Paul

2,0761 gold badge10 silver badges16 bronze badges

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

3 Comments

tixastronauta Over a year ago

I'm sorry. It was a typo on my post. I am using the dot. Edited.

Paul Over a year ago

It works on jsonpath.com. Have you tried the bracket notation $.['foo:oo']? It's probably confused by ":". Note: I would also try double quotation if single quotes are not working.

Paul Over a year ago

Updated my answer.

Collectives™ on Stack Overflow

MySQL JSON Path with semicolon in element

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related