This query retrieves and formats user notes from the ‘plugin_store_rows’ table, associating each note with its corresponding user ID, creation date, content, and creator’s ID. The result is a comprehensive list of user notes with relevant details.
WITH user_notes AS (
SELECT
REPLACE(key, 'notes:', '')::int AS user_id,
notes.value->>'created_at' AS created_at,
notes.value->>'raw' AS user_note,
notes.value->>'created_by' AS created_by
FROM plugin_store_rows,
LATERAL json_array_elements(value::json) notes
WHERE plugin_name = 'user_notes'
ORDER BY 2 DESC
)
SELECT
un.user_id,
un.created_at::date,
un.user_note,
un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id