Working with JSON
Many apps send and receive data as JSON, a text format like {"name": "Emma", "age": 26}. CQL can turn rows into JSON, and JSON into rows.
SELECT JSON: rows out as JSON
Add the word JSON after SELECT:
SELECT JSON * FROM employees WHERE id = 5;
Output:
[json]
------------------------------------------------------------------------------
{"id": 5, "age": 26, "department": "IT", "gender": "female", "name": "Emma"}
(1 rows)
Each row comes back as one JSON document, in a single column named [json]. Your app can send it straight to a web page.
It works with a list of columns too:
SELECT JSON name, department FROM employees
WHERE id = 5;
Output:
[json]
--------------------------------------
{"name": "Emma", "department": "IT"}
(1 rows)
INSERT JSON: rows in from JSON
Put JSON and the document after the table name:
INSERT INTO employees JSON
'{"id": 40, "name": "Noah", "age": 27,
"department": "IT"}';
SELECT * FROM employees WHERE id = 40;
Output:
id | age | department | gender | name
----+-----+------------+--------+------
40 | 27 | IT | null | Noah
(1 rows)
Each key in the document goes into the column with the same name. The document had no gender, so that column is null.
The values must match the column types. Text goes in double quotes ("IT") and numbers have no quotes (27). The whole document is one text value, so it sits inside single quotes.
JSON in CQL is only a different way to write and read the same table. The columns and their types do not change.
In the exercise, show the employee with id 3 as a JSON document.