Store/index arbitrary length list of [number, number] tuples

Hi,

I have a JSONB column which holds data like the following:

[[5567, 6288290106102], [7922, 1565590655290]

The general format is:
[[container_nr, position], ...]

[container_nr, position] tuples can be in this column one or more times.
I have to search (equality matching is fine with multiple OR statements, <> would be better, but the docs say it’s not yet supported) for the container_nr.

What’s the most efficient way to solve this (storage and index-wise)?
Currently I do two JSONB columns, one for the above and another with the container_nrs, like: [container_nr1, container_nr2, ...] and use an inverted index on the latter.
Is there a better way to do this?

Hey @bra,

Would you be able to share you DDL with us. It would be very useful to see how your table is structured, also, if you could send us over an EXPLAIN of a few queries. This would show us if the queries are using the index you created.

Thanks,

Ron

Sure!

CREATE TABLE objects (
	store INTEGER NOT NULL, 
	hash BYTEA NOT NULL, 
	id INTEGER NOT NULL, 
	size INTEGER, 
	orig_size INTEGER, 
	compression INTEGER, 
	atime INTEGER DEFAULT CAST(now() as int) NOT NULL, 
	containers JSON, 
	positions JSON, 
	check_dup BOOLEAN, 
	PRIMARY KEY (store, hash, id)
)
CREATE TABLE names (
	store INTEGER NOT NULL, 
	bucket INTEGER NOT NULL, 
	id VARCHAR NOT NULL, 
	object_hash BYTEA NOT NULL, 
	object_id INTEGER NOT NULL, 
	atime INTEGER DEFAULT CAST(now() as int) NOT NULL, 
	PRIMARY KEY (store, bucket, id), 
	FOREIGN KEY(store, object_hash, object_id) REFERENCES objects (store, hash, id)
)

The hash here is a crypto hash (used for dedup), so multiple names can point to the same object.
containers has the keys of positions, like: [2073, 5622]
while positions are like: {"2073": 1165332320204, "5622": 8976057250499}

Another question: can I use the computed column feature to store positions JSON keys in containers as a JSON array?
Effectively I would like to put a secondary index only to the keys of a JSON column, not the whole structure.