From: | Yasin Sari <yasinsari81(at)googlemail(dot)com> |
---|---|
To: | Chris Withers <chris(at)simplistix(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: json aggregation question |
Date: | 2017-02-28 17:19:35 |
Message-ID: | CADhAfgPhiXKS14KHfxFXsAmi2=0auh5J2rmDZn36bDpz85QhGw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)simplistix(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Withers | 2017-02-28 17:28:38 | Re: json aggregation question |
Previous Message | David G. Johnston | 2017-02-28 17:10:43 | Re: Making a unique constraint deferrable? |