Derived table with JSON_ARRAYAGG

CREATE OR REPLACE TABLE test (

	id INT,

	name VARCHAR(255),

	attribute VARCHAR(255)

);

INSERT INTO test (id, name, attribute) VALUES

(1, 'John Doe', 'height: 6ft'),

(1, 'John Doe', 'age: 30'),

(2, 'Jane Smith', 'height: 5.5ft'),

(2, 'Jane Smith', 'age: 28'),

(3, 'Alice Johnson', 'height: 5.7ft'),

(3, 'Alice Johnson', 'age: 35'),

(4, 'Bob Brown', 'height: 6.1ft'),

(4, 'Bob Brown', 'age: 40'),

(5, 'Charlie Davis', 'height: 5.9ft'),

(5, 'Charlie Davis', 'age: 32');

SELECT id, name, attr FROM (

	SELECT id, name, JSON_ARRAYAGG(attribute ORDER BY attribute) attr FROM test GROUP BY id, name

) t

WHERE JSON_VALUE(attr, '$[0]') = 'age: 30';

-- returns empty result

SELECT id, name, attr, JSON_VALUE(attr, '$[0]') = 'age: 30' as v FROM (

	SELECT id, name, JSON_ARRAYAGG(attribute ORDER BY attribute) attr FROM test GROUP BY id, name

) t

HAVING v = 1;

-- returns one result

CREATE OR REPLACE TEMPORARY TABLE tmp

SELECT id, name, JSON_ARRAYAGG(attribute ORDER BY attribute) attr FROM test GROUP BY id, name;

SELECT * FROM tmp WHERE JSON_VALUE(attr, '$[0]') = 'age: 30';

-- returns one result