CODEWITHSUNDEEP
sqljsonpostgresql
Karol Katanowski
Karol Katanowski

Reputation: 186

Select specified text from JSON column

I have a table, for example writing in psql. This table has a column json (text type). It contains text like this:

writing:[{"variableName":variableValue ...}]

variableValues are different types, including text ,bigint and date.

I want to get all rows from writing where variableName has the value 2.

I'm using this select:

select * from writing where json::json->>'variableName' = '2' limit 5

This select returns me 0 rows, but there are a lot of data in this table, which should pass this condition. Any idea what is wrong, or maybe you have better statement.

Im using limit 5 because need just 5 rows.

Upvotes: 0

Views: 144

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

You'll have to prepend a { and append a } to make it a JSON like you intend. As it is, it will become a single JSON string.

Then you'll have to access the attribute as

('{' || json || '}')::json->'writing'->1->>'variableName'

Upvotes: 1

Related Questions