Recursive CTEs & Analytics — Series 3

Preview — 3 of 10 questions

What does this query compute?

javascript

SELECT customer_id,
  jsonb_agg(product_name) FILTER (WHERE status = 'shipped') AS shipped_items
FROM orders
GROUP BY customer_id;

AA JSON array of every product_name for that customer, ignoring status entirely

BA JSON array containing only the product_names of that customer's rows where status = 'shipped' — FILTER restricts which rows feed the aggregate, exactly like it does for any other aggregate function, and it composes normally with jsonb_agg

CA syntax error — FILTER cannot be combined with jsonb_agg, only with numeric aggregates like SUM

DOne JSON object per customer with a shipped boolean key

Given CREATE TYPE address AS (city text, zip text);, what does this return?

javascript

SELECT * FROM jsonb_populate_record(NULL::address, '{"city": "Paris", "zip": "75001"}'::jsonb);

AThe raw jsonb value, unchanged, cast to address

BAn error, because NULL::address cannot be used as a template

CA single row of type address, with city = 'Paris' and zip = '75001' — the NULL::address argument supplies only the shape (column names and types) to populate from the JSON object's matching keys, missing keys becoming NULL

DTwo rows, one per key in the JSON object

Given data = '["a", "b", "c"]'::jsonb, what's the difference between these two?

javascript

SELECT jsonb_array_elements(data) FROM t;
SELECT jsonb_array_elements_text(data) FROM t;

AThey're identical; _text is just a deprecated older name

BThe first errors on non-object array elements; the second doesn't

Cjsonb_array_elements_text only works on arrays of numbers, converting them to their text representation

Djsonb_array_elements expands the array into one row per element, each still a jsonb value (so a string element comes back as "a", quotes included); jsonb_array_elements_text does the same but unwraps each element into a plain text value (a, no quotes)

Answer all 10 questions (7 more), see explanations for every answer, and track your score.