Select specified text from JSON column

Question

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.

Unrelated, but: JSON data is best stored in a column defined as jsonb, not as text. Then you would notice that writing:[{"variableName":variableValue ...}] is not a valid JSON value. — user330315
– user330315, Commented Apr 4, 2019 at 7:52
Noticed that but data structure is far beyond so changing that will be way too time consuming. Anyway I appreciate the tip. — Karol Katanowski
– Karol Katanowski, Commented Apr 4, 2019 at 7:58

Laurenz Albe · Accepted Answer · 2019-04-04 07:42:23Z

1

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'

answered Apr 4, 2019 at 7:42

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

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

4 Comments

Karol Katanowski Over a year ago

With Your example it pop out that argument of WHERE must be type boolean, not type text because as u said it will become a single JSON string.

Laurenz Albe Over a year ago

Right. Your condition would then be WHERE /* my expression */ = '2'.

Karol Katanowski Over a year ago

Ye. Now everything is working as it should. Much thanks for help. Have one question for your statement what exactly is doing 1 in 'writing'->1->>'variableName'

Laurenz Albe Over a year ago

It gets the first element of a JSON array (the [...]).

Collectives™ on Stack Overflow

Select specified text from JSON column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related