0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.