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

NULL values from JSON NESTED PATH with JSON column

    XMLWordPrintable

    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

          Activity

            People

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

              Dates

              Created:
              Updated:

                Git Integration

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