점 표기법 및 큰따옴표를 사용한 Oracle 12c JSON 쿼리 문제

점 표기법 및 큰따옴표를 사용한 Oracle 12c JSON 쿼리 문제

저는 "IS JSON"의 체크 제약 조건이 있는 "Metadata" 열이 있는 "EvMetadata" 테이블을 가지고 있습니다. 테이블과 그 열은 설계상 DUBLE COUTE로 생성됩니다.

SQL은 Oracle에서 수행할 JSON 작업을 지정하지 않습니다.

select 
  m."Metadata"
from "EvMetadata" m

아래에서 볼 수 있는 것처럼 메타데이터 열은 JSON 데이터인 내용만 표시합니다.

하지만 json 쿼리를 아래와 같이 발행하면 오류가 납니다.

select 
  m."Metadata"."FileName"
from "EvMetadata" m

방금 점 표기를 사용하여 "파일 이름"을 추가했습니다.위에서 볼 수 있듯이 "FileName"은 유효한 json 필드입니다.그럼 왜 오류가 난 거지?

오류는

ORA-00904: "M" "메타데이터""파일 이름": 잘못된 식별자 00904입니다.00000 - "%s: 잘못된 식별자" *원인: *조치:줄에서 오류 발생: 2 열: 3

데이터베이스 개체가 이중 따옴표로 선언되는 특정 시나리오에서 점 표기법을 사용하는 Oracle의 JSON 쿼리 지원 버그가 될 수 있습니까?제가 그것이 사실일 수도 있다고 의심하는 이유는 점 표기법을 사용하지 않는 다음과 같은 동등한 쿼리가 작동하기 때문입니다.

select 
  JSON_VALUE(m."Metadata", '$.FileName')
from "EvMetadata" m

점 표기법이 작동하려면 열에 "IS JSON" 검사 제약 조건이 있어야 합니다.

다음은 문서에서 발췌한 내용입니다.

각 json_key는 유효한 SQL 식별자여야 하며 열에는 올바른 형식의 JSON 데이터가 포함되도록 하는 is json 체크 제약 조건이 있어야 합니다.이러한 규칙 중 하나라도 존중되지 않으면 쿼리 컴파일 시 오류가 발생합니다.(오류가 발생하지 않도록 체크 제약 조건이 있어야 하지만 활성화되어 있을 필요는 없습니다.제약 조건을 비활성화하면 이 오류가 발생하지 않습니다.)

이것이 어떻게 작동하는지 확인하기 위해 실시한 테스트 예는 다음과 같습니다.

--create a table to put stuff in
create table foo (
 json varchar2(4000)
);
--------------------------------
Table FOO created.

--insert test value
insert into foo(json) values('{"attr1":5,"attr2":"yes"}');
commit;
--------------------------------
1 row inserted.
Commit complete.


--try some selects
--no table alias, no constraint, borked
select json.attr1 from foo;
--------------------------------
Error starting at line : 12 in command -
select json.attr1 from foo
Error at Command Line : 12 Column : 8
Error report -
SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--with table alias, no constraint, borked
select a.json.attr1 from foo a;
--------------------------------
Error starting at line : 15 in command -
select a.json.attr1 from foo a
Error at Command Line : 15 Column : 8
Error report -
SQL Error: ORA-00904: "A"."JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--add our constraint
alter table foo add constraint json_isjson check (json is json);
--------------------------------
Table FOO altered.

--no table alias, with constraint, borked
select json.attr1 from foo;
--------------------------------
Error starting at line : 21 in command -
select json.attr1 from foo
Error at Command Line : 21 Column : 8
Error report -
SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--table alias and constraint, works!
select a.json.attr1 from foo a;
--------------------------------
ATTR1                                                                          
--------------------------------------------------------------------------------
5                                                                               

다른 사용자가 이 문제를 알게 될 경우 Oracle Support에 참고 사항 2192052.1로 문서화되어 있습니다.

기본적으로 NOT NULL 제약 조건으로 작성된 열에서 Dot Notation이 작동하지 않는 버그입니다.

사용할 경우:

CREATE TABLE foo.bar (id NUMBER NOT NULL, json_doc CLOB NOT NULL CHECK (json_doc IS JSON));

실행 시 오류가 발생합니다.

SELECT a.json_doc.elementName FROM foo.bar a;

하지만 만약 그렇다면:

CREATE TABLE foo.bar (id NUMBER NOT NULL, json_doc CLOB CHECK (json_doc IS JSON));
ALTER TABLE bar MODIFY (json_doc NOT NULL);

Dot 표기법이 작동합니다.

견적은 필요 없습니다. 이렇게 하면 됩니다.

select m.Metadata.FileName from EvMetadata m

공식 문서의 예를 참고하시기 바랍니다.

SELECT po.po_document.PONumber FROM j_purchaseorder po;

SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;

언급URL : https://stackoverflow.com/questions/31570987/oracle-12c-json-query-issue-with-dot-notation-and-double-quotes