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

Document JSON concepts as they are used and interpreted in MariaDB

    XMLWordPrintable

Details

    Description

      Documentation for JSON functions routinely refers to the concepts json_doc, path and value without describing them (or if such a description exists, it is not referred to).

      If the implication is that our definition is identical to JSON standard, in general it is not true, and even when it is, it can become wrong in future – the standard evolves and our implementation won't be able to keep up with it. It is also not user-friendly, as different concepts are described in different standards, e.g. JSON docs (texts), values etc. in JSON standard, while path in SQL standard. And definitions from JSON standard cannot be applied literally, they have to be interpreted in a certain way.

      Here is the simplest example. JSON standard says:

      Here are three small JSON texts containing only values:

      "Hello world!"

      42

      true

      In our documentation, we call JSON texts "JSON documents". Like this:

      JSON_VALID(value)

      Description

      Indicates whether the given value is a valid JSON document or not. Returns 1 if valid, 0 if not, and NULL if the argument is NULL.

      So, if we take it all literally, each of these should be true:

      JSON_VALID("Hello world!");
      JSON_VALID(true);
      JSON_VALID(42);
      

      But it is not so. From MariaDB perspective, the correct syntax would be

      JSON_VALID('json_text');
      

      instead of

      JSON_VALID(json_text);
      

      where json_text is what JSON standard specifies.

      So, when we try to run the above, we'll get

      MariaDB [test]> select JSON_VALID("Hello world!");
      +----------------------------+
      | JSON_VALID("Hello world!") |
      +----------------------------+
      |                          0 |
      +----------------------------+
      1 row in set (0.001 sec)
      

      because it should really be

      MariaDB [test]> select JSON_VALID('"Hello world!"');
      +------------------------------+
      | JSON_VALID('"Hello world!"') |
      +------------------------------+
      |                            1 |
      +------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> 
      

      However, if we check other two examples, they will appear to be valid, which is even more confusing:

      MariaDB [test]> select JSON_VALID(42);
      +----------------+
      | JSON_VALID(42) |
      +----------------+
      |              1 |
      +----------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select JSON_VALID(true);
      +------------------+
      | JSON_VALID(true) |
      +------------------+
      |                1 |
      +------------------+
      1 row in set (0.001 sec)
      

      As explained by serg, it happens not because these are really valid JSON texts, but because MariaDB features implicit type conversion .
      That is, 42 is converted into '42' which is indeed a valid value. For true it is probably even a 2-step conversion.
      It makes things even more unclear, because when two of three examples work and the third one doesn't, it looks like misfunctioning.

      It has already caused confusion among users and will likely cause more. I experienced it, too.

      JSON path is even worth, because in SQL standard it is the whole language. Obviously not all of it has been implemented, but which part has is impossible to guess.

      To summarize, I suggest

      • to document all concepts which syntax for JSON functions in the KB refers to, and synchronize it with the references;
      • when/if possible and applicable, mention intentional deviations from the standard (with the version of the standard the implementation is otherwise based on);
      • additionally make a note of the influence of implicit data cast with redirection to the corresponding page.

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            elenst Elena Stepanova
            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.