CODEWITHSUNDEEP
jsonoracle-database
Matthew Moisen
Matthew Moisen

Reputation: 18299

Oracle How to Normalize a Json Array

How can I normalize a field that contains a pure JSON array, as opposed to a JSON object with a JSON array inside of it?

CREATE TABLE j (
    measurements CLOB
    CONSTRAINT ensure_json CHECK (measurements IS JSON)
);
INSERT INTO j VALUES ('[{"start_time": 1234, "start_time": 5678}]');

I've tried variations of the following which all resulted in syntax errors:

SELECT s.*
FROM j, 
    json_table(j.measurements, ':q' COLUMNS (start_time INT PATH '$.start_time')) AS s;

-- Where :q is '*', or '.', or '$.*', or '$' or '$.*' or '$.[*]'

In the case where you have a JSON array inside of a JSON object, this query will normalize it successfully:

INSERT INTO j VALUES ('{"foo": [{"start_time": 1234}, {"start_time": 5678}]}');
SELECT s.*
FROM j, 
    json_table(j.measurements, '$.foo[*]' COLUMNS (start_time INT PATH '$.start_time')) AS s;

START_TIME
     1234
     5678

Upvotes: 0

Views: 663

Answers (1)

mark d drake
mark d drake

Reputation: 1515

The JSON PATH expression must be known at query compilation time, they cannot be supplied dynamically at execution time, hence the JSON PATH expressions are required to be constant strings, not binds.

Also we don't like objects where the same key occurs multiple times

However is this what you are looking for

SQL> WITH
  2  MY_TABLE AS
  3  (
  4    SELECT '[{"start_time": 1234},{"start_time": 5678}]' JSON_DOC
  5      from DUAL
  6  )
  7  select START_TIME
  8    from MY_TABLE,
  9         JSON_TABLE (
 10           JSON_DOC,
 11           '$[*]'
 12           COLUMNS
 13             START_TIME number(4) path '$.start_time'
 14         )
 15  /
      1234
      5678

SQL>

Upvotes: 1

Related Questions