5

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.

1 Answer 1

8

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.