Postgresql – merge json online postgresql 10

aggregatejsonpostgresql

I have the following tables:

 create table tags (
        id bigserial primary key,
        file_id bigint,
        key varchar,
        num bigint,
        str varchar)

create table meta_info (
    id bigserial primary key,
    filename varchar,
    created timestamp with time zone,
    version varchar
    )

file_id is a reference for meta_info.id

I need to find a filename and gather all tags for it.
The result must be the following:

[{"key": "val", "key1": "val1", "filename": "A/B/C/1.exe"}, 
{"key3": 5, "key2": ["val2"], "filename": "A/B/C/2.exe"}]

Tags table can contain the following data:

file_id key   num  str
2343423 "key" null "val"
2343423 "key1" null "val1"
4564576 "key3" 5    null
4564576 "key2" null ["val2"]::text

I cannot keep tags as jsonb since json query doesn't support a complex structure like 'ilike' search smwhere inside json or when I need a deep search inside json and compare value (less than some value), etc.

Also index will be not used for the query (only @>, ? can be used for index)

When we have a value as a numerical data, we will have null in the str column.
When we have a value as a string or dict, list, etc., we will have null in the num column.

So I need to gather all tags but I cannot merge list of jsons online.
Here is my SQL query:

 SELECT filename, json_array_elements(json_agg(json_build_object(tags.key, COALESCE(tags.str, CAST(tags.num as varchar(100)))))::text) as val FROM
(SELECT *, rank() OVER (partition by filename order by created desc) AS rank
        FROM meta_info m
        where m.filename like 'DATASETS/14.0/%'
        ) tt
        inner join tags on tags.file_id=tt.id
        WHERE tt.rank = 1
    group by tt.filename

I have the following result:

DATASETS/14.0/Error_1012/input.exe/ [{"key1" : "val1"},{"key2" : "val2"}]
DATASETS/14.0/Error_1011/input.exe/ [{"key3" : ["val3"]},{"key4" : 5}]

So the second column is the list of dictionaries, I need to merge them "online". How can I do it in the SQL query?

Best Answer

I have found a solution:

select array_to_json(array_agg(js.row)) as json from 
( SELECT json_build_object('name', filename)::jsonb || jsonb_object_agg(tags.key, COALESCE(tags.str, CAST(tags.num as varchar(100)))) as row FROM 
(SELECT *, rank() OVER (partition by filename order by created desc) AS rank 
 FROM meta_info m where m.filename like 'DATASETS/14.0/%' ) tt 
inner join tags on tags.file_id=tt.id WHERE tt.rank = 1 group by tt.filename) as js

Related Solutions

Postgresql – Fetch nested results as JSON PostgreSQL 9.2

Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.

It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the record pseudo-type.

Here's what I came up with:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,   
        d.name, 
        json_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT  
            c.id,       
            c.name,     
            json_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id) 
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id) 
        GROUP BY c.id 
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

The general idea here is, at every level of object nesting where you're producing an array of objects, use json_agg in a group_by. The json_agg function implicitly calls row_to_json to transform a rowtype into json. In the SELECT clause specify aliases for the synthetic columns so the json key names are correct when the row is fed into the outer level.

As the outer level isn't aggregated, use row_to_json over a subquery instead of using json_agg. If you want a single json result instead of a set of json rows, you can change row_to_json to json_agg in the outer level.

I've only tested on 9.3, as that's what I have installed and sqlfiddle seems to be having some issues.

Update: Unfortunately json_agg doesn't exist on 9.2, it was added in 9.3. However, this is a simple enough case that you can just use array_agg instead, so this should work in 9.2:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,   
        d.name, 
        array_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT  
            c.id,       
            c.name,     
            array_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id) 
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id) 
        GROUP BY c.id 
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

Ideally PostgreSQL would support this sort of query more naturally, without the subqueries, but I think we'd need a new join type, or at least some functions to use with lateral queries. It all plans out into a civilised query plan anyway (though it'd be better with suitable indexes):

                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on r  (cost=1050.26..1282.94 rows=720 width=106)
   ->  GroupAggregate  (cost=1050.26..1273.94 rows=720 width=188)
         ->  Merge Join  (cost=1050.26..1226.42 rows=7704 width=188)
               Merge Cond: (d.id = dc.deviceid)
               ->  Index Scan using devices_pkey on devices d  (cost=0.15..58.95 rows=720 width=82)
               ->  Sort  (cost=1050.11..1069.37 rows=7704 width=110)
                     Sort Key: dc.deviceid
                     ->  Merge Join  (cost=403.16..552.77 rows=7704 width=110)
                           Merge Cond: (c_row.id = dc.componentid)
                           ->  Subquery Scan on c_row  (cost=253.38..285.63 rows=720 width=110)
                                 ->  GroupAggregate  (cost=253.38..278.43 rows=720 width=286)
                                       ->  Sort  (cost=253.38..258.73 rows=2140 width=286)
                                             Sort Key: c.id
                                             ->  Hash Join  (cost=44.75..135.00 rows=2140 width=286)
                                                   Hash Cond: (cm.manufacturerid = m.id)
                                                   ->  Hash Join  (cost=26.20..87.03 rows=2140 width=86)
                                                         Hash Cond: (cm.componentid = c.id)
                                                         ->  Seq Scan on component_manufacturers cm  (cost=0.00..31.40 rows=2140 width=8)
                                                         ->  Hash  (cost=17.20..17.20 rows=720 width=82)
                                                               ->  Seq Scan on components c  (cost=0.00..17.20 rows=720 width=82)
                                                   ->  Hash  (cost=13.80..13.80 rows=380 width=208)
                                                         ->  Seq Scan on manufacturers m  (cost=0.00..13.80 rows=380 width=208)
                           ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
                                 Sort Key: dc.componentid
                                 ->  Seq Scan on device_components dc  (cost=0.00..31.40 rows=2140 width=8)
(25 rows)

Postgresql: Aggregate objects into an (json) array (subquery issues)

If you are on 9.4 something like this might be what you are after:

select json_object(array_agg(id)::text[],array_agg(rw)::text[])
from( select id
           , ( select to_json(array_agg(row_to_json(t)))
               from (select typ,prop from bgb where id=b.id) t ) rw
      from bgb b
      group by id ) z;

Best Answer

Related Solutions

Postgresql – Fetch nested results as JSON PostgreSQL 9.2

Postgresql: Aggregate objects into an (json) array (subquery issues)

Related Question