MYSQL JSON - how do I access nested JSON object that's indexed by numerical key?

Question

I am trying to extract JSON document subtree that's indexed by numerical key.

My JSON string:

{
    "pk": 20,
    "tree": {
        "100": {
            "values": [
                1, 2, 3
            ]
        },
        "abc" => 999
    }
}

My code:

$session = mysql_xdevapi\getSession("mysqlx://root:letmein@localhost");
$schema = $session->getSchema('test');
$coll = $schema->getCollection('myColl');
$expr = mysql_xdevapi\Expression('$.tree.*');
$result = $coll->find('$.pk=20')->fields(['$.tree[100]'])->execute();

Using '$.tree[100]' results in

    [
        'tree' => null
    ]

Using '$.tree.*' results in

    [
        'tree' => [
            0 => [
                1, 2, 3
            ],
            1 => 999
        ]
    ]

Using '$.tree.abc' results in

    [
        'tree' => [
            'abc' => 999
        ]
    ]

So, '$.tree.abc' works, but '$.tree[100]' doesnt.

Question. How can I access values key using '$.tree[100]' expression?

Thanks!

Try: ... $coll->find('$.pk=20')->fields(['$.tree."100"'])->execute();. — wchiquito
– wchiquito, Commented Jul 23, 2019 at 17:37
nope: mysql_xdevapi\CollectionFind::fields(): Error while parsing, details: CDK Error: After seeing '$.tree."100" AS ', looking at '100': Expected identifier after AS (cdk:8). How would you do that search from Javascript mysqlsh? — temuri
– temuri, Commented Jul 23, 2019 at 17:43
This works for me from mysqlsh-js: mysqlsh-js> db.myColl.find("pk = 20").fields("tree.'100'");. — wchiquito
– wchiquito, Commented Jul 24, 2019 at 20:57
Yes, it does. But only from mysqlsh. There's a bug in PHP extension. — temuri
– temuri, Commented Jul 24, 2019 at 21:42

marinesovitch · Accepted Answer · 2019-08-19 08:05:05Z

2

Thnx for report, following case:

$expr = mysql_xdevapi\Expression('$.tree."100"'); 
$result = $coll->find('$.pk=25')->fields($expr)->execute();

will be supported in mysql_xdevapi v8.0.18 which is planned for Oct 14.

edited Aug 19, 2019 at 8:05

answered Aug 19, 2019 at 7:58

marinesovitch

462 bronze badges

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

Collectives™ on Stack Overflow

MYSQL JSON - how do I access nested JSON object that's indexed by numerical key?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related