Concat / Search on multiple jsonb nested values

Question

I have jsonb column in my PgSql table with data like these :

{
  "group": [
    {
      "child1": "test",
      "child2": "test"
    },
    {
      "child1": "test2",
      "child2": "lorem ipsum"
    }
  ]
}

"group" can be multiple and is compounded of nested children data.

Is it possible to concat "child1" data in one select request or to check if one of nested "child1" is like %test%.

Thanks in advance.

What exactly is the output you want? The whole JSON value for all rows that match the condition? Or only the part of the JSON that matches the condition? — user330315
– user330315, Commented Nov 2, 2020 at 9:50

Barbaros Özhan · Accepted Answer · 2020-12-01 08:34:38Z

2

You can use such a SELECT statement including two functions with CROSS JOINs:

SELECT j2.*
  FROM t
 CROSS JOIN JSONB_EACH(data) AS j
 CROSS JOIN JSONB_ARRAY_ELEMENTS(data->'group') AS j2
 WHERE j2::TEXT like '%"test"%'

in order to look strictly for string with exact value "test" without any heading or trailing parts. If you need whether that value contains a substring test, then get rid of double quotes which wraps up it.

Demo

edited Dec 1, 2020 at 8:34

answered Nov 2, 2020 at 8:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

3 Comments

Benito103e Over a year ago

Thanks, I did a test and it works. I have to dig a little deeper into the functioning of CROSS JOIN which is not trivial. And the reason why we use j2.value.

Barbaros Özhan Over a year ago

you're welcome @Benito103e . Nice catch, yes, value is redundant.

Benito103e Over a year ago

With this solution, I also need to add a groupBy if a row has 2 children1 meeting the criteria.

user330315 · Accepted Answer · 2020-11-02 08:25:15Z

1

I would use an exists condition:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.data -> 'group') as g(element)
                cross join jsonb_each_text(g.element) as x(key,value)
              where x.key = 'child1'
                and x.value like '%test%')

Starting with Postgres 12 you can use a SQL/JSON path expression:

select *
from the_table t
where data @@ '$.group[*].child1 like_regex "test"'

answered Nov 2, 2020 at 8:25

user330315

2 Comments

Benito103e Over a year ago

Woww this Pg12 feature is amazing ! Using a path expression will be so helpful.

Benito103e Over a year ago

Using "cross join" and "exist" together seems to meet completely my need. Now I have to see how to translate it into ORM language =)

Collectives™ on Stack Overflow

Concat / Search on multiple jsonb nested values

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related