Previous Next JavaScript must be enabled to correctly display this content

## 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.