current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I'm trying to query a table with a JSON column which will always hold an array of "primitive" values (i.e. integers, strings, booleans -- not objects or arrays).

My query should be similar to [ref2], but I can't do ->>'id' because I'm not trying to access a JSON object but the value itself.

In the [ref1] fiddle (blatant fork from the above), there's and incomplete query... I'd like to query all things which contain 3 among its values.

Even more so, I'd like some rows to have arrays of strings, other rows to have arrays of integers, and other ones arrays of booleans... So casting is undesiderable.

I believe ->> returns the original JSON value type, but I need the "root" object... That is, my JSON value is [1,2,3,4], using json_array_elements should yield e.g. 2, but that is a JSON type according to my tests.

Upgrading to 9.4 is planned in the near future, but I haven't read anything yet that gave me a clue jsonb would help me.

UPDATE: at the moment, I'm (1) making sure all values are integers (mapping non-integers values to integers), which is suboptimal; (2) querying like this:

SELECT *
  FROM things, json_array_elements(things.values) AS vals
  WHERE vals.value::text::integer IN (1,2,3);

I need the double casting (otherwise it complains that cannot cast type json to integer).

ref1: http://sqlfiddle.com/#!15/5febb/1

ref2: How to query an array of JSON in PostgreSQL 9.3?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Rather than using json_array_elements you can unpack the array with generate_series, using the ->> operator to extract a text representation.

SELECT things.*
FROM things
CROSS JOIN generate_series(0, json_array_length(values) - 1) AS idx
WHERE values ->> idx = '1'
GROUP BY things.id;

This is a workaround for the lack of json_array_elements_text in 9.3.

You need an operator(=) for json to do this without either messing with casting or relying on the specific textual representations of integers, booleans, etc. operator(=) is only available for jsonb. So in 9.3 you're stuck with using the text representation (so 1.00 won't = 1) or casting to a PostgreSQL type based on the element type.

In 9.4 you could use to_json and the jsonb operator(=), e.g.:

SELECT things.*
    FROM things
    CROSS JOIN generate_series(0, json_array_length(values) - 1) AS idx
    WHERE (values -> idx)::jsonb = to_json(1)::jsonb
    GROUP BY things.id;

 id |             date              | values  
----+-------------------------------+---------
  1 | 2015-08-09 04:54:38.541989+08 | [1,2,3]
(1 row)
share|improve this answer
    
For 9.4+, the containment operators give somewhat simpler solutions, like values::jsonb @> jsonb '1' OR values::jsonb @> jsonb '"foo"' – pozs Aug 19 at 15:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.