Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.16
-
None
-
Ubuntu 20.04
Description
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)
|