Deep JSON query with partial path in PGSQL JSONB?

Question

Given a number of JSON document like this:

  {
    id: some_id,
    l1: {
      f1: [
        {
          c1: foo,
          c2: bar
        },
        {
          c1: foo1,
          c2: bar1
        },
      ],
      f2: [
        {
          c3: baz,
          c4: bar
        },
      ],    
    }
  }

How can I query PostgreSQL JSONB for f1....c1: foo1 -- ie lX is not given nor is the list position of the c1-c2 subdocument.

This is not a duplicate of Deep JSON query with partial path in MySQL 5.7? since that is about MySQL and this one is about PgSQL JSONB.

Dzmitry Savinkou · Accepted Answer · 2016-02-18 00:17:47Z

2

Here you need to iterate over the list of elements for path {l1,f1} #> - operator gets JSON object at specified path; after that, check if any of sub-documents contains '{"c1":"foo1"}'::jsonb element - @> (operator checks if the left JSON value contains within it the right value) :

WITH t(val) AS ( VALUES
  ('{
      "id": "some_id",
      "l1": {
        "f1": [
          {
            "c1": "foo",
            "c2": "bar"
          },
          {
            "c1": "foo1",
            "c2": "bar1"
          }
        ],
        "f2": [
          {
            "c3": "baz",
            "c4": "bar"
          }
        ]
      }
  }'::JSONB)
)
SELECT f1_array_element AS output
FROM
  t,jsonb_array_elements(t.val#>'{l1,f1}') AS f1_array_element
WHERE f1_array_element @> '{"c1":"foo1"}'::JSONB;

Result:

            output            
------------------------------
 {"c1": "foo1", "c2": "bar1"}
(1 row)

UPDATE

If we don't know about about exact lX location, we need to iterate over each subdocument, and than iterate over each fX; Query will be the following:

SELECT count(*)
FROM
  t,
  jsonb_each(t.val#>'{l1}') AS fX_sub_doc,
  jsonb_array_elements(fX_sub_doc.value) AS cX_sub_doc
WHERE
  cX_sub_doc @> '{"c1":"foo1"}';

edited Feb 18, 2016 at 0:17

answered Feb 16, 2016 at 23:50

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chx Over a year ago

Yes but you presumed l1 and I tried to mention that won't be given. Do I need to UNION this for every possible lX?

chx Over a year ago

thanks! But I think the jsonb_each() argument is incorrect?

Collectives™ on Stack Overflow

Deep JSON query with partial path in PGSQL JSONB?

1 Answer 1

UPDATE

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related