DROP TABLE IF EXISTS cards;

CREATE TABLE cards (
id integer NOT NULL,
board_id integer NOT NULL,
data jsonb
);

INSERT INTO cards VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
INSERT INTO cards VALUES (2, 1, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}');
INSERT INTO cards VALUES (3, 1, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}');
INSERT INTO cards VALUES (4, 1, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}');
INSERT INTO cards VALUES (5, 1, '{"name": "Hang paintings", "tags": ["Improvements", "Office"], "finished": false}');

/*
Listing names of a card
*/
SELECT data->>'name' AS name FROM cards;

/*
Listing elements of an array
*/
SELECT jsonb_array_elements_text(data->'tags') as tag
-- info -> 'Rating' ->> 'Rating_ID' AS Αξιολόγηση,
-- info -> 'Rating' ->> 'Rating_App_Value' AS Τιμή,
-- info -> 'Rating' ->> 'Rating_App_Date' AS Ημερομηνία,
-- OPERATE.Operating_System_Name
FROM cards
WHERE data ? 'ingredients' ;

/*
Filtering results. In the example below our “data” column has a property called “finished”,
and we only want results where finished is true.
*/
SELECT * FROM cards WHERE data->>'finished' = 'true';

/*
Checking for column existence. Here we'll find a count of the records where the data column
contains a property named "ingredients"
*/
SELECT count(*) FROM cards WHERE data ? 'ingredients';

/*
List the tags for row with id=3
*/
SELECT
jsonb_array_elements_text(data->'tags') as tag
FROM cards
WHERE id = 3;