DB2 FOR LUW / DB2FORLUW / A50 - DB2

When a table containing nested arrays is queried using JSON sql, 
incorrect result set is returned. The result set contains the 
values of the array not being searched for in the query. 
 
Ex: 
 
1. Create a table as follows: 
 
CREATE TABLE db2inst1.T1  ( 
		  C1 VARCHAR(256 OCTETS) NOT NULL , 
		  C2 BLOB(16777216) LOGGED NOT COMPACT , 
		 ORGANIZE BY ROW; 
 
 2. Populate the table with data containing nested 
arrays: 
 
Json: 
{ 
  "typeReference": {"typeId": "1"}, 
  "value": { 
    "name": "john", 
    "address1": { 
      "typeReference": {"typeId": "5"}, 
      "value": [ 
        { 
          "street": { 
            "typeReference": {"typeId": "5"}, 
            "value": "100 east" 
          } 
        } 
       ] 
    }, 
    "address2": { 
      "typeReference": {"typeId": "5"}, 
      "value": [ 
        { 
          "street": { 
            "typeReference": {"typeId": "5"}, 
            "value": "200 west" 
          } 
        }, 
        { 
          "street": { 
            "typeReference": {"typeId": "5"}, 
            "value": "201 west" 
          } 
        }, 
        { 
          "street": { 
            "typeReference": {"typeId": "5"}, 
            "value": "202 west" 
          } 
        } 
      ] 
    } 
  } 
} 
 
3. Run a select as follows: 
 
select JSON_VAL(T.C2,'value.name','s:30') as name, 
JSON_VAL(x.value, 'street.value' , 's:128') as street 
from db2inst1.T1 T, 
table( 
SYSTOOLS.JSON_TABLE_BINARY(T.C2,'value.address1.value','s:256')) 
x 
 
One rows was expected to be returned as only address1 was 
specified however, 2 rows were returned: 
 
name  street 
----------------------- 
john     100 east 
john     200 west 
 
 
 
This issue exists for SYSTOOLS.JSON_TABLE() UDF as well: 
 
db2> select value from 
TABLE(SYSTOOLS.JSON_TABLE((SYSTOOLS.JSON2BSON('{"json":{"adr1":{ 
"c1":[{"s":{"d2":"100 east"}}]},"adr2":{"c2":[{"s":{"d2": "200 
west"}}] },"adr3":{"c3":[{"s":{"d3": "300 
north"}}]}}}')),'json.adr2.c2','s:30')); 
 
VALUE 
-------------------------------------------------- 
{s:{d2:"200 west"}} 
 
Above query works fine as it has 'adr3.c3' not 'adr3.c2'. 
However if sql c3 is changed to c2  in above query wrong results 
might be returned: 
 
db2> select value from 
TABLE(SYSTOOLS.JSON_TABLE((SYSTOOLS.JSON2BSON('{"json":{"adr1":{ 
"c1":[{"s":{"d2":"100 east"}}]},"adr2":{"c2":[{"s":{"d2": "200 
west"}}] },"adr3":{"c2":[{"s":{"d3": "300 
north"}}]}}}')),'json.adr2.c2','s:30')); 
 
VALUE 
-------------------------------------------------- 
{s:{d2:"200 west"}} 
{s:{d3:"300 north"}} 
 
The problem is that  result from adr3.c2 is returned as well, 
even though when we query for adr2.c2.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW 10.5 FP8                                  * 
****************************************************************

Change sql to not specify first array as the first search 
criteria. 
 
 
EX: 
 
select JSON_VAL(T.C2,'value.name','s:30') as name, 
JSON_VAL(x.value, 'street.value' , 's:128') as street 
from db2inst1.T1 T, 
table( 
SYSTOOLS.JSON_TABLE_BINARY(T.C2,'value.address2.value','s:256')) 
x 
 
This works even when selecting multiple arrays as long as the 
1st array is not specified as first search criteria.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

07.06.2016
11.11.2016
22.03.2017