merge json online postgresql 10

Question

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?

Answer 1

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