CODEWITHSUNDEEP
sqldatabasepostgresql
BACode
BACode

Reputation: 81

Search JSON column for JSON that contains a specific value

I have a postgresql database with a table called choices, in the choices table I have a column called json that contains JSON entries, for example: [1,2,3]

I need a query that returns all entires that contains a specific value.

For example I have the following entries:

I want to get all entries that contain the value 1 so it would return: [1,2,3] [6,7,1]

Thanks,

Upvotes: 1

Views: 145

Answers (1)

S-Man
S-Man

Reputation: 23676

demo: db<>fiddle

The json_array_elements_textfunctions expands the json arrays into one row each element (as text). With that you can filter it by any value you like.

SELECT 
    json_data
FROM choices, json_array_elements_text(json_data) elem
WHERE value = '1'

Documentation: JSON functions

Please notice that "json" is a the name for the json type in PostgreSQL. You should better rename your column to avoid some conflicts. (I called mine json_data)

Upvotes: 1

Related Questions