CODEWITHSUNDEEP
jsonpostgresqlaggregate-functionsjsonb
Bestows In Constructing
Bestows In Constructing

Reputation: 325

Get all jsonb values in one column for usage in to_tsvector

I got a table jsonb table t_1, that contains column column_1 with jsonb type and i need to convert in to table with string, that is concatination of jsonb values

Here is a script

    CREATE TABLE t_1 (
       ID serial NOT NULL PRIMARY KEY,
       column_1 jsonb NOT NULL
    );


    INSERT INTO t_1 (column_1)
    VALUES
    (
       '{ "01": "Lily Bush", "03": "prod2uct"}'
    ),
    (
       '{ "41": "Josh William", "12": "product7"}'
    ),
    (
       '{ "07": "Mary Clark", "items" : "product2"}'
    );

I tried this:

SELECT distinct jsonb_array_elements_text(column_1 -> jsonb_object_keys(column_1)) AS Actor FROM t_1

But this return 'unable to extract elements from scalar'

I tried this:

SELECT tiket, string_agg(column_2, ', ') as list FROM( SELECT column_1 ->> jsonb_object_keys(column_1) as column_2, id as tiket FROM t_1 ) as foo1 GROUP BY tiket

but here is inner select

How can i get all jsonb values in one column without inner select, something like in first query?

I need it to use in to_tsvector

I need to use it in

setweight(
to_tsvector('simple', column_with_json::text),
'A'
)

But column_with_json::text not what i need, i need to get values, without keys

Any examples?

Upvotes: 1

Views: 613

Answers (1)

klin
klin

Reputation: 121794

Use jsonb_each_text() in a lateral join: 

SELECT id, string_agg(value, ', ') AS list
FROM t_1
CROSS JOIN jsonb_each_text(column_1)
GROUP BY id

 id |          list          
----+------------------------
  1 | Lily Bush, prod2uct
  2 | product7, Josh William
  3 | Mary Clark, product2
(3 rows)

If you want to use the resulting aggregated value in a condition, use HAVING clause, e.g.: 

SELECT id, string_agg(value, ', ') AS list
FROM t_1
CROSS JOIN jsonb_each_text(column_1)
GROUP BY id
HAVING string_agg(value, ', ') LIKE '%Lily%'

or a derived table and WHERE clause in a wrapper query: 

SELECT *
FROM (
    SELECT id, string_agg(value, ', ') AS list
    FROM t_1
    CROSS JOIN jsonb_each_text(column_1)
    GROUP BY id
    ) s
WHERE list LIKE '%Lily%'

Both approaches are basically equivalent, in typical cases they generate the same query plan. In both the aggregation is calculated only once.

Upvotes: 1

Related Questions