MySQL JSON_TABLE Multiple Nested Paths

Question

I have the following query:

SELECT * FROM
    JSON_TABLE(
    '{"TrackingIds":[-1,-2],"Matrices":[22,23]}' 
    ,"$" COLUMNS(
        NESTED path "$.Matrices[*]" COLUMNS (Matrices INT path '$'),
        NESTED path "$.TrackingIds[*]" COLUMNS (TrackingId INT path '$')
    )) AS  j;

Which yields:

Matrices    TrackingId
22             \N
23             \N
\N             -1
\N             -2

How would I get it produce the below:

Matrices    TrackingId
22             -1
23             -2

Almost like it is a Key Value lookup. Thanks, -Tanner

kierans · Accepted Answer · 2021-03-16 06:36:18Z

2

This is by design. From the MySQL 8 manual:

Sibling nested paths—that is, two or more instances of NESTED [PATH] in the same COLUMNS clause—are processed one after another, one at a time. While one nested path is producing records, columns of any sibling nested path expressions are set to NULL. This means that the total number of records for a single match within a single containing COLUMNS clause is the sum and not the product of all records produced by NESTED [PATH] modifiers.

You need an outer query to group related rows together, or not use NESTED PATHs

answered Mar 16, 2021 at 6:36

kierans

2,2532 gold badges21 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

MySQL JSON_TABLE Multiple Nested Paths

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