D
D
diejsel2015-08-13 21:05:41
PostgreSQL
diejsel, 2015-08-13 21:05:41

PostgreSQL->jsonb how to use field aliases in a condition?

Hello everyone, especially PostgreSQL gurus.
There is a request:

SELECT jsonb_array_elements(documents->'policy') AS policies
FROM (
  SELECT jsonb_array_elements(documents) AS documents
  FROM test
) AS policy
--WHERE policies->>'date_from' > '1424621049'

With the where field commented out, the query returns a list of policies (json objects) contained in the documents (jsonb) field of the test table. It is worth making a condition, as it swears at an indefinite field policies. I did not find anything specific on the Internet on this issue, except that aliases for fields in the conditions are not supported (there is such a thing in mysql).
From here a question how to implement the given request with a condition?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
diejsel, 2015-08-13
@diejsel

I decided so, but there may still be options and suggestions. And then writing such a structure in a framework through ORM is somehow not "ethical" or something.

SELECT * 
FROM
(
  SELECT jsonb_array_elements(documents->'policy') AS policies
  FROM (
    SELECT jsonb_array_elements(documents) AS documents
    FROM test
  ) AS policy
) AS result
WHERE policies->>'date_from' > '1424621049'

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question