Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27899

NULL values from JSON NESTED PATH with JSON column

Details

    Description

      The following SELECT statement:

          SELECT u.id as any_id, u.*, attrs.*
          FROM SyncopeUser u, JSON_TABLE(COALESCE(plainAttrs, '[{}]'), '$[*]' COLUMNS (
          plainSchema VARCHAR(255) PATH '$.schema',
          NESTED PATH '$.values[*]' COLUMNS (
          binaryValue LONGBLOB PATH '$.binaryValue',
          booleanValue INT PATH '$.booleanValue',
          dateValue BIGINT(20) PATH '$.dateValue',
          doubleValue DOUBLE PATH '$.doubleValue',
          longValue BIGINT(20) PATH '$.longValue',
          stringValue VARCHAR(255) PATH '$.stringValue'),
          attrUniqueValue JSON PATH '$.uniqueValue')
          ) AS attrs
      

      returns only NULL values for the attrUniqueValue column, even though the value from the plainAttrs column are as follows:

      [
        {
          "values": [
            {
              "stringValue": "Rossini"
            }
          ],
          "schema": "surname"
        },
        {
          "values": [
            {
              "dateValue": 1243288800000
            },
            {
              "dateValue": 1274824800000
            }
          ],
          "schema": "loginDate"
        },
        {
          "uniqueValue": {
            "stringValue": "Gioacchino Rossini"
          },
          "schema": "fullname"
        },
        {
          "uniqueValue": {
            "stringValue": "rossini@apache.org"
          },
          "schema": "userId"
        }
      ]
      

      The same exact query run against MySQL 8.0 produces the expected result, e.g. for some rows the uniqueValue column being a JSON column with values:

          {
            "stringValue": "rossini@apache.org"
          }
      

      or

          {
            "stringValue": "Gioacchino Rossini"
          }
      

      Attachments

        Issue Links

          Activity

            In reality it doesn't return NULL as a result, it returns NULL as an indication of an error. It is clearer when ERROR ON ERROR is used:

            create table SyncopeUser (plainAttrs JSON);
            insert into SyncopeUser values (
            '[
              {
                "values": [
                  {
                    "stringValue": "Rossini"
                  }
                ],
                "schema": "surname"
              },
              {
                "values": [
                  {
                    "dateValue": 1243288800000
                  },
                  {
                    "dateValue": 1274824800000
                  }
                ],
                "schema": "loginDate"
              },
              {
                "uniqueValue": {
                  "stringValue": "Gioacchino Rossini"
                },
                "schema": "fullname"
              },
              {
                "uniqueValue": {
                  "stringValue": "rossini@apache.org"
                },
                "schema": "userId"
              }
            ]');
             
            SELECT attrs.*
              FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS (
              attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR)
              ) AS attrs;
             
            # Cleanup
            DROP TABLE SyncopeUser;
            

            10.6 4e1ca388

            query 'SELECT attrs.*
            FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS (
            attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR)
            ) AS attrs' failed: ER_JSON_TABLE_SCALAR_EXPECTED (4178): Can't store an array or an object in the scalar column 'attrUniqueValue' of JSON_TABLE 'attrs'.
            

            In MySQL it works, because they have the logic for JSON column type in a JSON table, but MariaDB doesn't, it treats it the same way as TEXT.
            I remember discussions about it before, but not the result, so I'll leave it to holyfoot – maybe there is already a task for implementing it.

            elenst Elena Stepanova added a comment - In reality it doesn't return NULL as a result, it returns NULL as an indication of an error. It is clearer when ERROR ON ERROR is used: create table SyncopeUser (plainAttrs JSON); insert into SyncopeUser values ( '[ { "values": [ { "stringValue": "Rossini" } ], "schema": "surname" }, { "values": [ { "dateValue": 1243288800000 }, { "dateValue": 1274824800000 } ], "schema": "loginDate" }, { "uniqueValue": { "stringValue": "Gioacchino Rossini" }, "schema": "fullname" }, { "uniqueValue": { "stringValue": "rossini@apache.org" }, "schema": "userId" } ]' );   SELECT attrs.* FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS ( attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR) ) AS attrs;   # Cleanup DROP TABLE SyncopeUser; 10.6 4e1ca388 query 'SELECT attrs.* FROM SyncopeUser u, JSON_TABLE(plainAttrs, ' $[*] ' COLUMNS ( attrUniqueValue JSON PATH ' $.uniqueValue ' ERROR ON ERROR) ) AS attrs' failed: ER_JSON_TABLE_SCALAR_EXPECTED (4178): Can 't store an array or an object in the scalar column ' attrUniqueValue ' of JSON_TABLE ' attrs'. In MySQL it works, because they have the logic for JSON column type in a JSON table, but MariaDB doesn't, it treats it the same way as TEXT. I remember discussions about it before, but not the result, so I'll leave it to holyfoot – maybe there is already a task for implementing it.

            This issue looks fixed in 10.10.2: can you please confirm?

            ilgrosso Francesco Chicchiriccò added a comment - This issue looks fixed in 10.10.2: can you please confirm?
            rucha174 Rucha Deodhar added a comment -

            Already fixed in 10.6

            rucha174 Rucha Deodhar added a comment - Already fixed in 10.6

            People

              rucha174 Rucha Deodhar
              ilgrosso Francesco Chicchiriccò
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.