codejump
EasySQL

Extract a JSON Field

SQLDatabasesJSON

Products store their variable attributes as a JSON blob in a single TEXT
column, rather than one column per possible attribute.

Table: `products`

idnameattrs
1Widget{"color":"red","price":19.99}
2Gadget{"color":"blue","price":29.99}

Write a query that returns each product's id, name, and the
color pulled out of attrs.

Expected output (columns: id, name, color), ordered by id:

1|Widget|red
2|Gadget|blue

Hint — SQLite's JSON functions use $.field path syntax:

json_extract(attrs, '$.color'). (SQLite 3.27, which this platform runs,

predates the ->/->> operator syntax — that needs 3.38+ — so

json_extract() is the only option here.)

Sample tests

Test #1Two products with different colors
Input: "CREATE TABLE products (id INTEGER, name TEXT, attrs TEXT);\nINSERT INTO products VALUES\n (1, 'Widget', '{\"color\":\"red\",\"price\":19.99}'),\n (2, 'Gadget', '{\"color\":\"blue\",\"price\":29.99}');"
Output: "1|Widget|red\n2|Gadget|blue"
Test #2Single product
Input: "CREATE TABLE products (id INTEGER, name TEXT, attrs TEXT);\nINSERT INTO products VALUES\n (1, 'Solo', '{\"color\":\"green\",\"price\":5}');"
Output: "1|Solo|green"
← All challenges