ORACLE -LIKE condition in JSON_VALUE (BLOB FIELD) not working?

Question

I have one table with a blob field called BLOBFIELD.

This:

SELECT A.ID
FROM   A AP 
WHERE  JSON_VALUE(
         AP.BLOBFIELD format json,
         '$.arrays.AAA.arrays.BBB.fields.Name'
       ) = 'peter'

works fine when I use equals. But this:

SELECT A.ID
FROM   A AP 
WHERE  JSON_VALUE(
         AP.BLOBFIELD format json,
         '$.arrays.AAA.arrays.BBB.fields.Name'
       ) LIKE 'peter%'

and:

SELECT A.ID
FROM   A AP 
WHERE  (JSON_VALUE(
         AP.BLOBFIELD format json,
         '$.arrays.AAA.arrays.BBB.fields.Name'
       )) LIKE '%peter%'

I don´t have error, but I think that it not work because AAA and BBB are arrays
I found regex_like but this function receive such as first value a field, but I don´t have field I have a json path.

SELECT A.ID FROM A AP 
WHERE JSON_VALUE( AP.BLOBFIELD format json, '$.arrays.AAA[*].arrays.BBB[*].fields.Name' ERROR ON ERROR ) LIKE 'peter%'

are not working. How can I get it to work using LIKE?

By "not working", do you mean you get an error, or just no results returned? — IMSoP, Feb 16 '17 at 14:36
this link http://stackoverflow.com/a/16301995/3377472 show an example. — Jorge T, Feb 16 '17 at 14:42
will be dbms_lob.instr(AP.BLOBFIELD,utl_raw.CAST_TO_RAW('hello'),1,1)>0 But I have a json_value JSON_VALUE( AP.BLOBFIELD format json, '$.arrays.AAA.arrays.BBB.fields.Name' ) — Jorge T, Feb 16 '17 at 14:42

mark d drake · Answer 1 · 2017-04-10T15:06:39.603

JSON_VALUE cannot be used to access a key that occurs more than once in a docment. Eg if the key or any of its parents are arrays then the content cannot be accessed via JSON_VALUE unless the JSON_PATH expression uses indices to identify a particular member of the array.

We need to use JSON_TABLE to solve this one...

SQL> WITH A as
  2  (  select 1 ID,
  3           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Max"}}},{"BBB":{"fields":{"Name":"Mike"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Mark"}}},{"BBB":{"fields":{"Name":"Matt"}}}]}}]}' BLOBFIELD
  4      from DUAL
  5     union all
  6     select 2 ID,
  7           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Pat"}}},{"BBB":{"fields":{"Name":"Pete"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Paul"}}},{"BBB":{"fields":{"Name":"Pascal"}}}]}}]}' BLOBFIELD
  8      from DUAL
  9  )
 10  SELECT distinct AP.ID
 11   FROM  A AP,
 12         JSON_TABLE(
 13           AP.BLOBFIELD,
 14           '$.arrays[*]'
 15           COLUMNS
 16           NESTED PATH
 17           '$.AAA.arrays[*]'
 18           COLUMNS (
 19             NAME VARCHAR2(32) PATH '$.BBB.fields.Name'
 20           )
 21        )
 22  where NAME LIKE '%Pete%'
 23
SQL>       /
         2

Just for completeness.

The reason you got no rows with JSON_VALUE is the default "NULL ON ERROR" behavior. This can be see by adding the ERROR OR ERROR clause as can be seen below

SQL> WITH A as
  2  (  select 1 ID,
  3           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Max"}}},{"BBB":{"fields":{"Name":"Mike"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Mark"}}},{"BBB":{"fields":{"Name":"Matt"}}}]}}]}' BLOBFIELD
  4      from DUAL
  5     union all
  6     select 2 ID,
  7           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Pat"}}},{"BBB":{"fields":{"Name":"Pete"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Paul"}}},{"BBB":{"fields":{"Name":"Pascal"}}}]}}]}' BLOBFIELD
  8      from DUAL
  9  )
 10  SELECT AP.ID
 11  FROM   A AP
 12  WHERE  JSON_VALUE(
 13           AP.BLOBFIELD format json,
 14           '$.arrays.AAA.arrays.BBB.fields.Name'
 15         ) LIKE 'peter%'
 16  /

no rows selected

SQL> WITH A as
  2  (  select 1 ID,
  3           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Max"}}},{"BBB":{"fields":{"Name":"Mike"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Mark"}}},{"BBB":{"fields":{"Name":"Matt"}}}]}}]}' BLOBFIELD
  4      from DUAL
  5     union all
  6     select 2 ID,
  7           '{"arrays":[{"AAA":{"arrays":[{"BBB":{"fields":{"Name":"Pat"}}},{"BBB":{"fields":{"Name":"Pete"}}}]}},{"AA
A":{"arrays":[{"BBB":{"fields":{"Name":"Paul"}}},{"BBB":{"fields":{"Name":"Pascal"}}}]}}]}' BLOBFIELD
  8      from DUAL
  9  )
 10  SELECT AP.ID
 11  FROM   A AP
 12  WHERE  JSON_VALUE(
 13           AP.BLOBFIELD format json,
 14           '$.arrays.AAA.arrays.BBB.fields.Name'
 15           ERROR ON ERROR
 16         ) LIKE 'peter%'
 17  /
FROM   A AP
       *
ERROR at line 11:
ORA-40470: JSON_VALUE evaluated to multiple values


SQL>

score 0 · Answer 2 · answered Feb 16 '17 at 22:31

0

I ran JSON_VALUE using LIKE and it worked perfectly. I tested on 12.1.0.2 RELEASE and later versions. What happens if you use ERROR ON ERROR clause in your JSON_VALUE?

SELECT A.ID FROM A AP WHERE JSON_VALUE( AP.BLOBFIELD format json, '$.arrays.AAA.arrays.BBB.fields.Name' ERROR ON ERROR ) LIKE 'peter%'

are you seeing an error?

answered Feb 16 '17 at 22:31

Beda Hammerschmidt

416
3
6

I don´t have error, but I think that it not work because AAA and BBB are arrays Example. SELECT A.ID FROM A AP WHERE JSON_VALUE( AP.BLOBFIELD format json, '$.arrays.AAA[*].arrays.BBB[*].fields.Name' ERROR ON ERROR ) LIKE 'peter%' – Jorge T Feb 17 '17 at 13:06

ORACLE -LIKE condition in JSON_VALUE (BLOB FIELD) not working?

2 Answers2