PostgreSQL Question posted in
The official documentation can be found here.

By what may I replace ->0, ->1 operators to list all the array values in a json array I'm querying with PostgreSQL?

I’m querying this json field, named attributes, with PostgreSQL:

{
   "rule": {
       "requisite": [
           { 
              "link": {
                  object: "a",
                  description: "I'm the A object"
              }
           },
           {
              "link": {
                  object: "b",
                  description: "I'm the B object"
              }
           },
           {
              "link": {
                  object: "c",
                  description: "I'm the C object"
              }
           }
       ]
   }
}

If I run the following request, varying ->0, ->1, ->2 operators, I can reach each single array items at its index, one by one:

WITH rules AS (
   SELECT attributes->'rule'->'requisite'->0->'link'->'object' AS object
      FROM mytable
)
SELECT * FROM rules

And I can have a, or b or c.
But this is clumsy.

I’ve searched for something like ->*,
attempted a jsonb_array_elements(attributes->'rule'->'requisite')
but didn’t find a way to list all my objects conveniently, yet. I would like to receive for result:

object
------
a
b
c
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Yes, @Bergi, you're right.
    I eventually succeeded.

    WITH requisites AS (
   SELECT attributes->'rule'->'requisite' AS requisite
      FROM mytable
)
select jsonb_array_elements(requisite)->'link'->'object' from requisites

  2. 0

    I would use a JSONPATH function:

    SELECT t.j
FROM mytable
   CROSS JOIN jsonb_path_query(
                 mytable.attributes,
                 '$.rule.requisite[*].link.object'
              ) AS t(j);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search