Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11.11, 10.6, 11.4, 11.8
-
None
-
AlmaLinux release 8.10
Description
Hello,
I use a simple select to query a complex JSON structure. I also use the json_table function. The result is also as expected.
Since the table with the JSON data has a larger size, I wanted to save this select as a view. When I then query the view, the result is unfortunately not identical to that from the simple select. It seems that the json_table function in a view returns a different result.
Here is a simple example:
SELECT |
case when json_type(jt.singleValue) = 'BOOLEAN' then jt.singleValue end booleanValue |
,case when json_type(jt.singleValue) = 'STRING' then jt.singleValue end stringValue |
,jt.numericValue
|
,jt.unitName
|
,jt.singleValue
|
,json_type(jt.singleValue) typeSingleValue
|
from json_table('[{"value":{"value":6.0E0}},{"value":{"unit":{"name":"V"},"value":1.2E1}},{"value":true},{"value":"Beispieltext"}]', '$[*]' |
columns (
|
singleValue json path '$.value', |
numericValue double path '$.value.value', |
unitName varchar(255) path '$.value.unit.name' |
)) jt
|
the expected result:
| booleanValue | stringValue | numericValue | unitName | singleValue | typeSingleValue |
|---|---|---|---|---|---|
| 6.0 | {"value":6.0E0} | OBJECT | |||
| 12.0 | V | {"unit":{"name":"V"},"value":1.2E1} | OBJECT | ||
| true | true | BOOLEAN | |||
| "Beispieltext" | "Beispieltext" | STRING |
Here is the view definition:
create or replace view jsonTestView |
(booleanValue, stringValue, numericValue, unitName, singleValue, typeSingleValue)
|
as
|
select * |
from ( |
SELECT |
case when json_type(jt.singleValue) = 'BOOLEAN' then jt.singleValue end booleanValue |
,case when json_type(jt.singleValue) = 'STRING' then jt.singleValue end stringValue |
,jt.numericValue
|
,jt.unitName
|
,jt.singleValue
|
,json_type(jt.singleValue) typeSingleValue
|
from json_table('[{"value":{"value":6.0E0}},{"value":{"unit":{"name":"V"},"value":1.2E1}},{"value":true},{"value":"Beispieltext"}]', '$[*]' |
columns (
|
singleValue json path '$.value', |
numericValue double path '$.value.value', |
unitName varchar(255) path '$.value.unit.name' |
)) jt
|
) x
|
the wrong result:
select * from jsonTestView
|
| booleanValue | stringValue | numericValue | unitName | singleValue | typeSingleValue |
|---|---|---|---|---|---|
| 6.0 | |||||
| 12.0 | V | ||||
| true | true | BOOLEAN | |||
| Beispieltext |
I think that the different results are not intentional.
best regards
Karsten
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-