1

I've found the solution for more complex than this but I'm not able to make this work...

I've a column called data of type json. The JSON structure is as follows:

{"actions": ["action1","action2","action3", ..., "actionN"]}

So, let's say I've 3 rows with the following data:

{"actions": ["work","run"]}
{"actions": ["run","eat","sleep", 'walk']}
{"actions": ["eat","run","work"]}

I want to retrieve the rows where work is included in actions array.

I've tried something similar to what is posted here: Query for element of array in JSON column, but since each element inside the array is just a json string, I got stuck there.

Then, I tried something like:

SELECT * from table t WHERE 'work' in ...

but this also failed to get the values as an string array to put it there.

Using PostgreSql 9.3.

Community
  • 1
  • 1
MerLito
  • 101
  • 8
  • Somehow use http://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array with unnest – Mihai Jun 26 '15 at 07:34

1 Answers1

1

Since you are using the json type, which is just a string internally, the simplest solution is:

SELECT * FROM table WHERE strpos(data::text, 'work') > 0;

It defeats the purpose of using json in the first place, but it is (very likely) faster than the json parser in this simple case.

A json solution would be:

SELECT * FROM (
  SELECT *, unnest(data->"actions")::text AS action FROM table) AS foo
WHERE action = 'work';
Patrick
  • 29,357
  • 6
  • 62
  • 90