Using SQL commands on User-Supplied Metadata

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

Note:

You can only write user-supplied metadata through APIs, see Using User-Supplied Metadata.

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

SELECT last_write_metadata($t) AS Metadata FROM stream_acct $t

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

Output:

{"Metadata":{"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 user-supplied metadata

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

Exaplanation: In the query above, you fetch the episode details of a user account and use the last_write_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 user-supplied metadata in the WHERE clause.

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

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