[MDEV-34081] View containing JSON_TABLE does not return JSON - Jira

XML

Word

Printable

Extracting part of a JSON with JSON_TABLE works in direct query but not as view.

SELECT * FROM JSON_TABLE('

    "caption": "First Element",

    "value": 1

},

    "caption": "Second Element",

    "value": 2

', '$[*]' COLUMNS(

	caption VARCHAR(200) PATH '$.caption',

	whole_block JSON PATH '$')) t;

brings

caption         whole_block

--------------  ----------------------------------------------------------

First Element   {

                    "caption": "First Element",

                    "value": 1

Second Element  {

                    "caption": "Second Element",

                    "value": 2

which is what I expected.

Creating the view out if it does not return the block:

CREATE OR REPLACE VIEW test_view AS SELECT * FROM JSON_TABLE('

    "caption": "First Element",

    "value": 1

},

    "caption": "Second Element",

    "value": 2

', '$[*]' COLUMNS(

	caption VARCHAR(200) PATH '$.caption',

	whole_block JSON PATH '$')) t;

SELECT * FROM test_view;

Result:

caption         whole_block

--------------  -------------

First Element   (NULL)

Second Element  (NULL)

is duplicated by

MDEV-34644 CREATE VIEW: NULL values from JSON NESTED PATH with JSON column

MDEV-36455 JSON_TABLE generates a different result in a view than a simple select

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