Using SQL commands on row metadata

Note:

This feature has been made available to you on a "preview" basis so that you can get early access and provide feedback. It is intended for demonstration and preliminary use only. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this feature and will not be responsible for any loss, costs, or damages incurred due to the use of this feature.

With the SQL commands you can retrieve row metadata through queries, and you can also use row metadata in the WHERE clause to filter rows.

Note:

You can only write row metadata through APIs, see Using user-defined row metadata.

Example: Fetch row metadata for all the rows in the table.

SELECT row_metadata($t) AS RowMetadata FROM stream_acct $t

Explanation: The query above fetches the metadata for all the rows in the stream_acct table.

Output:

{"RowMetadata":{"modified_by":"John Doe","reviewed_in":"Q1","update_reason":"Account details updated"}}

Example: Fetch the episode info of a user account based on the modified_by field in the row metadata

SELECT $t.acct_data.contentStreamed[1].seriesInfo[0].episodes[1] AS content FROM stream_acct $t WHERE row_metadata($t).modified_by="John Doe"

Exaplanation: In the query above, you fetch the episode details of a user account and use the row_metadata() to filter records that John Doe modified.

Output:

{"content":{"date":"2022-03-08","episodeID":30,"episodeName":"Merci","lengthMin":42,"minWatched":42}}

Example: Delete data from the table using row metadata in the WHERE clause.

DELETE FROM stream_acct $t WHERE row_metadata($t).reviewed_in="Q1"

Explanation: The query above deletes the row from stream_acct table where reviewed_in field in the row's metadata has the value Q1.