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?