Join against JSON array values in a column

sql

SELECT a.id, je.value AS role
FROM accounts a,
     json_each(a.roles_json) AS je
WHERE je.value = 'admin';

Explanation

Great for analytics and migrations, though highly queried JSON fields are often better normalized or indexed via generated columns.

Learn the surrounding workflow

Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.

Same sheet · prioritizing Update and expand JSON

Turn array elements into result rows.

Open In sheetsqlsame section

Patch a document with json_set().

Open In sheetsqlsame section

Delete one path from a document.

Open In sheetsqlsame section

Read one property from a JSON document.

Open In sheetsql1 tag match

Read a JSON path with operator syntax.

Open In sheetsql1 tag match

Check whether a string contains valid JSON.

Open In sheetsql1 tag match