MySQL JSON – How to Group Multiple Rows as Array

arrayjsonMySQLmysql-8.0

This is my current Query:

SELECT innings_no,
Player_Name,
sum(NoBalls)                                                    as NB
    FROM (
           SELECT A.innings_no,
                  A.bowler,
                  A.Runs_In_Over + ifnull(B.Extra_runs, 0) = 0 as Maiden,
                  A.Balls - ifnull(B.NoBalls, 0)               as Balls,
                  A.Runs_In_Over + ifnull(B.Extra_runs, 0)     as Runs,
                  ifnull(C.Wicket, 0)                          as Wickets,
                  Zero,
                  Four,
                  Six,
                  ifnull(Wides, 0)                             as Wides,
                  ifnull(NoBalls, 0)                           as Noballs
           FROM (SELECT over_id,
                        ABBB.innings_no,
                        ABBB.bowler,
                        count(ABBB.ball_id)                 as Balls,
                        Sum(ABB.Runs_Scored)               As 'Runs_In_Over',
                        SUM(if(ABB.runs_scored = 0, 1, 0)) as Zero,
                        SUM(if(ABB.runs_scored = 4, 1, 0)) as Four,
                        SUM(if(ABB.runs_scored = 6, 1, 0)) as Six
                 FROM `database`.ball_by_ball ABBB
                        INNER JOIN `database`.batsman_scored ABB
                                   using (match_id, over_id, ball_id, innings_no)
                 where match_id = 981018
                 GROUP BY over_id, innings_no, bowler
                ) as A
                  LEFT JOIN
                (SELECT BBBB.over_id,
                        BBBB.innings_no,
                        BBBB.bowler,
                        SUM(if(BER.extra_type_id = 2, 1, 0)) as Wides,
                        SUM(if(BER.extra_type_id = 4, 1, 0)) as NoBalls,
                        Sum(BER.Extra_Runs)                  As 'Extra_runs'
                 FROM `database`.ball_by_ball BBBB
                        INNER JOIN `database`.extra_runs BER
                                   using (match_id, over_id, ball_id, innings_no)
                 WHERE match_id = 981018
                   and BER.Extra_Type_Id IN (2, 4)
                 GROUP BY over_id, innings_no, bowler
                ) as B
                using (over_id, innings_no, bowler)
                  LEFT JOIN
                (SELECT over_id,
                        CBBB.innings_no,
                        CBBB.bowler,
                        count(CBBB.bowler) as Wicket
                 FROM `database`.ball_by_ball CBBB
                        INNER JOIN `database`.wicket_taken CWT
                                   using (match_id, over_id, ball_id, innings_no)
                 WHERE match_id = 981018
                   and CWT.kind_out in (1, 2, 4, 6, 7, 8)
                 GROUP BY over_id, innings_no, bowler
                ) as C
                using (over_id, innings_no, bowler)
           order by innings_no, over_id
           limit 1000
         ) AS DATA
           INNER JOIN `database`.Player C
                      ON DATA.bowler = C.Player_Id
    GROUP BY innings_no, Player_Name;

This is Output:

+------------+---------------+------+
| innings_no | Player_Name   | NB   |
+------------+---------------+------+
|          1 | S Aravind     |    0 |
|          1 | Iqbal Abdulla |    0 |
|          1 | SR Watson     |    0 |
|          1 | CJ Jordan     |    0 |
|          1 | YS Chahal     |    0 |
|          1 | STR Binny     |    0 |
|          2 | P Kumar       |    0 |
|          2 | DS Kulkarni   |    0 |
|          2 | RA Jadeja     |    0 |
|          2 | SB Jakati     |    1 |
|          2 | DJ Bravo      |    0 |
|          2 | DR Smith      |    0 |
+------------+---------------+------+

I want to get output as below:

+------------+--------------------------------+------------+
| innings_no |           Player_Name          |     NB     |
+------------+--------------------------------+------------+
|          1 | ["S Aravind", .. ,"STR Binny"] | [0, .. ,0] |
|          2 | ["P Kumar", .. ,"DR Smith"]    | [0, .. ,0] |
+------------+--------------------------------+------------+

In the above output, I want to group my result by innings_no and convert those two rows to an array. I tried JSON_ARRAYAGG() function on Player_Name
but the output is not what I want, also it does not work on sum(NoBalls).

How can I solve this?

Best Answer

WITH cte AS (`your query text except last semicolon`)
SELECT innings_no,
       JSON_ARRAYAGG(Player_Name) Player_Names,
       JSON_ARRAYAGG(NB) NBs
FROM cte
GROUP BY innings_no;

If You need a strict position according Player_Name and NB, use

WITH cte AS (`your query text except last semicolon`)
SELECT DISTINCT innings_no,
                JSON_ARRAYAGG(Player_Name) OVER win Player_Names,
                JSON_ARRAYAGG(NB) OVER win NBs
FROM cte
WINDOW win AS (PARTITION BY innings_no ORDER BY Player_Name);

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
_{Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!}

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Need to select a JSON array element dynamically from a postgresql table

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

PostgreSQL – Query JSON Array of Objects Against Multiple Values

There is a way: combine the containment operator @> with the ANY construct:

SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.

You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?

Best Answer

Related Solutions

PostgreSQL – How to Turn JSON Array into Postgres Array?

Postgres 9.4 or newer

Function wrapper

Postgres 9.3 or older

Notes (outdated since pg 9.4)

PostgreSQL – Query JSON Array of Objects Against Multiple Values

Related Question