json aggregation question

Hi Chris,

Maybe there is an another better solution;

1. sending values into jsonb_array_elements to getting elements (lateral
join)
2. distinct to eliminate duplicates
3. regexp_replace to remove malformed Array literals
4. Casting into text array

SELECT
count(distinct tags ),
string_to_array(regexp_replace(string_agg(distinct elem::text ,
','),'\[*\"*\s*\]*','','g'),',') AS list
from thing as t, jsonb_array_elements(t.tags->'tag1') elem
where tags->'tag2'?'t2val1'

count | tag1
2 | {val1,val2,val3}

28 Şub 2017 Sal, 19:22 tarihinde, Chris Withers <chris@simplistix.co.uk>
şunu yazdı:

Hi All,

Given the following table:

# create table thing (id serial, tags jsonb);# \d thing
Table "public.thing"
Column | Type | Modifiers
--------+---------+----------------------------------------------------
id | integer | not null default nextval('thing_id_seq'::regclass)
tags | jsonb |

...and the following data:

insert into thing (tags) values ('{"tag1": ["val1", "val2"], "tag2":
["t2val1"]}');insert into thing (tags) values ('{"tag1": ["val3",
"val1"], "tag2": ["t2val1"]}');insert into thing (tags) values
('{"tag1": ["val2", "val1"], "tag2": ["t2val2"]}');

How can I aggregate the results of a query that equates to "show me the
number of matching rows and the set of tag1 value that have a tag2 value of
t2val1?

The closes I can get is:

# select count(*), json_agg(tags) from thing where tags->'tag2'?'t2val1';
count | json_agg

-------+--------------------------------------------------------------------------------------------------
2 | [{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}, {"tag1":
["val3", "val1"], "tag2": ["t2val1"]}](1 row)

...but I really want:

count | tag1
-------+-------------------------
2 | ["val1", "val2", "val3"](1 row)

cheers,

Chris

Thanks, this is closer, but regex really scares me for something like
this...

On 02/28/2017 08:21 AM, Chris Withers wrote:

How can I aggregate the results of a query that equates to "show me the
number of matching rows and the set of |tag1| value that have
a |tag2| value of |t2val1|?

...but I really want:

|count |tag1 -------+-------------------------2|["val1","val2","val3"](1row)|

Seems like this does the trick?:

SELECT COUNT(DISTINCT id),
json_agg(DISTINCT elem)
FROM (
SELECT id,
jsonb_array_elements_text(tags->'tag1') AS elem
FROM thing
WHERE tags->'tag2'?'t2val1'
) x;

You are looking to get always one result, right?

Yours,
Paul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general