CODEWITHSUNDEEP
jsonpostgresqljsonb
ML_Engine
ML_Engine

Reputation: 1195

Operator Does Not Exist - Postgres & JSON Select Query

I'm trying to retrieve and join data from a table containing JSONB data, where the JSON row is in the format:

{
    "id": "d57929b35216",
    "base" : {"legalName" : "SAPI S.P.A."}, 
    "name": "SAPI S.P.A.", 
}

Table ita_public

CREATE TABLE public.ita_public
(
    id integer NOT NULL DEFAULT nextval('ita_data_id_seq'::regclass),
    info jsonb NOT NULL,
    CONSTRAINT ita_data_pkey PRIMARY KEY (id)
)

Table ita_sn_private

CREATE TABLE public.ita_sn_private
(
    id integer NOT NULL DEFAULT nextval('ita_sn_private_id_seq'::regclass),
    supplier_name character varying COLLATE pg_catalog."default",
    supplier_streetadd character varying COLLATE pg_catalog."default",
    CONSTRAINT ita_sn_private_pkey PRIMARY KEY (id)
)

SELECT query prints three columns, joins on supplier name and nested name and search for name:

SELECT
priv.supplier_name,
priv.supplier_streetadd,
pub.info::json->'base'->'legalName'

FROM ita_sn_private as priv 
JOIN ita_public as pub ON (priv.supplier_name = pub.info::json->'name')

WHERE pub.info::json->>'name' = 'SAPI S.P.A.'

I get the error :

ERROR: operator does not exist:

character varying = json LINE 7: JOIN ita_public as pub ON (priv.supplier_name = pub.info::js...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

(With the little hat underneath the '=')

I tried to simplify the query to make sure that my json path is correct:

SELECT
info::json->'base'->'legalName'
FROM
ita_public
WHERE info::json->>'name' = 'SAPI S.P.A.'

Which works fine.

Can anyone assist with the JOIN statement? I'm not sure how to equate the two.

Upvotes: 5

Views: 8546

Answers (1)

user330315
user330315

Reputation:

The join condition is the reason:

on priv.supplier_name = pub.info::json->'name'

-> returns a JSON Object, not a string. You need to use ->> there

on priv.supplier_name = pub.info::json->>'name'

the same way you did in the WHERE clause

Upvotes: 8

Related Questions