Querying array of text in postgres

Question

I have an array type I want to store in Postgres. One of the major use cases I have is to see if any of the records has an array which has a string in it.

eg.

| A | ["NY", "Paris", "Milan"] |
| B | ["Paris", "NY"]          |
| C | []                       |
| D | ["Milan"]                |

Does there exist a row with Paris in the array? Which rows have Milan in the array? and so on.

I have 2 options on how to store the column. I can either make it a type text[] or convert it into a json as {"cities": ["NY", "Paris", "Milan"]} and then store as a JSONB field

However, I am not sure what would allow the fastest querying for the use case I have. Is there any one obviously better way of doing this? Am I tying myself down in any way by choosing one over the other? If I choose one over the other then how can I query the DB?

GMB · Accepted Answer · 2019-02-02 00:24:18Z

1

As you seem to be storing simple lists of values, I would recommend to use datataype Array over JSON, which better fits more complex cases (nested datastructures, associative arrays, ...).

To check for the value of an element at any position in the array, you can use array function ANY().

Here is a query that will return all records where the array stored in column cities contains 'Paris' :

SELECT t.* FROM mytable t WHERE 'Paris' = ANY(t.cities);

Yields :

id  cities
---------------------------
A   ["NY","Paris","Milan"]
B   ["Paris","NY"]

Demo on DB Fiddle

For more information :

answered Feb 2, 2019 at 0:24

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pappachino · Accepted Answer · 2019-02-02 00:17:43Z

0

I've noticed it is better to query JSONB, if it is a simple key-value store. As in for instance you want to store arbitrary info on a row that your not sure what the columns(keys) would be. info = {"a":"apple", "b":"ball"}

For use cases like yours, it would be better if you could design the db with simple tables so you could use JOINS and Indexes to your advantage.

You could restructure the tables like :

Location

id | name
----------
1  | Paris
2  | NY
3  | Milan

Other Table (with foreign key on location table)

user | location_id
--------------------
A    | 1
A    | 3
B    | 2

Using these set of tables it would be easy to query all users with location paris using JOINS.

answered Feb 2, 2019 at 0:17

pappachino

551 silver badge5 bronze badges

Collectives™ on Stack Overflow

Querying array of text in postgres

2 Answers 2

Comments

Location

Other Table (with foreign key on location table)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Location

Other Table (with foreign key on location table)

Comments

Your Answer

Sign up or log in

Post as a guest

Related