Question: postgres jsonb with issues

Question

postgres jsonb with issues

Answers 1
Added at 2016-11-30 00:11
Tags
Question

I am trying to build a query that will go into a table. Grab some data from within a jsonb column and then query against that data further.

WITH id_list AS (
  SELECT 
    data #>> '{embeds, related, content, pinned}' 
  FROM 
    content
  WHERE 
    site_id = 5 
   AND
    length(data #>> '{embeds, related, content, pinned}') > 0
  LIMIT 1
)

SELECT 
  id
FROM 
  content
WHERE 
  content.id IN id_list

When I run the first select it returns back

["446001", "445989", "445980", "445962", "445965"]

which is correct, why can't I query against the values in the array?

I was also trying to figure out if it was possible remove the limit on the first query and flatten the results to have one large array to query against.

Answers to

postgres jsonb with issues

nr: #1 dodano: 2016-11-30 00:11

The correct way to use WITH is to treat it as a table and do a select on it, like so:

WITH id_list AS (
  SELECT
    (data #>> '{embeds, related, content, pinned}') as ids
  FROM
    content
  WHERE
    site_id = 5
   AND
    length(data #>> '{embeds, related, content, pinned}') > 0
  LIMIT 1
)

SELECT
  id
FROM
  content
WHERE
  content.id IN (select ids from id_list);

See https://www.postgresql.org/docs/current/static/queries-with.html for further reference on how to use WITH correctly.

Source Show
◀ Wstecz