CODEWITHSUNDEEP
javadatabasepostgresqlspring-bootjpa
Akash Beura
Akash Beura

Reputation: 39

How to query this JSONB?

So I have a table

Node_Mapping(location_id:UUID, node_ids: jsonb)

The corresponding POJO for this is

class NodeMapping{
    UUID locationId;
    Set<String> nodeIds;
}

Example data in table is

UUID1 : ['uuid100', 'uuid101']
UUID2 : ['uuid103', 'uuid101']

So I want to make a query like, find out all the locationIds which contains this List<String> : ['uuid100', 'uuid200', 'uuid300']

Please help me to form the "IN" query with JSONB.

Also please help me to create a function/SP to take input this list of string and return the list of locationIds as an List/Set..

I tried the function but that fails.

 CREATE OR REPLACE FUNCTION find_location_ids_for_seller(_seller_id text[])
   RETURNS TABLE(location_id text)
   LANGUAGE plpgsql AS
 $func$
 BEGIN
    RETURN QUERY
    SELECT n.location_id::text
    FROM   node_mapping n
    WHERE  n.node_ids IN _seller_id;
 END
 $func$;

Upvotes: 0

Views: 58

Answers (1)

Edouard
Edouard

Reputation: 7065

The query to retrieve the node_ids which contain one or more elements of the list _seller_id :: text[] is :

CREATE OR REPLACE FUNCTION find_location_ids_for_seller(_seller_id text[])
   RETURNS TABLE(location_id text)
   LANGUAGE sql AS
 $func$
    SELECT n.location_id::text
    FROM   node_mapping n
    WHERE  n.node_ids :: jsonb ?| _seller_id ;
 $func$;

Upvotes: 1

Related Questions