Postgres: JOIN between JSON value and TEXT

Question

I am trying to make a join on 2 tables. The value I am trying to join is stored in a JSON column on one table and in a TEXT column in the other. Here is my query so far:

SELECT inv.data -> 'total_amount' as iTotalAmount, 
   inv.data -> 'due_date' as iDueDate,
   inv.data -> 'emission_date' as iEmissionDate,
   inv.data -> 'customer_name' as iCustomerName, 
   matc.ch_number as mCompanyHouseNumber,
   matc.acc_software_company_name as mAccSoftName
   FROM invoices inv, matching_ch_acc_software matc
   JOIN mCompanyHouseNumber on iCustomerName
   WHERE inv.rating_data IS NOT null

I am however getting the following error:

SQL Error [42P01]: ERROR: relation "mcompanyhousenumber" does not exist Position: 363

I assumed you cant use values defined in the query as part of the JOIN condition? What would be a walk around?

EDIT

updated query:

SELECT inv.data -> 'total_amount' as iTotalAmount, 
   inv.data -> 'due_date' as iDueDate,
   inv.data -> 'emission_date' as iEmissionDate,
   inv.data -> 'customer_name' as iCustomerName, 
   matc.ch_number as mCompanyHouseNumber,
   matc.acc_software_company_name as mAccSoftName
   FROM invoices inv, matching_ch_acc_software matc
   INNER JOIN matching_ch_acc_software
   ON (invoices.data -> 'customer_name')::text =  matching_ch_acc_software.acc_software_company_name
   WHERE inv.rating_data IS NOT null

error:

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "invoices" Hint: There is an entry for table "inv", but it cannot be referenced from this part of the query. Position: 397

Don't mix implicit and explicit joins, switch to explicit joins everywhere. — jarlh
– jarlh, Commented Mar 21, 2020 at 14:46
@jarlh sorry that is my mistake i pasted the wrong query. I have now eddited the post above — 0xtuytuy
– 0xtuytuy, Commented Mar 21, 2020 at 15:15
Explicit JOIN's are evaluated before implicit, comma separated joins. I.e. the ON clause above doesn't know about the invoices table. — jarlh
– jarlh, Commented Mar 21, 2020 at 15:31

jjanes · Accepted Answer · 2020-03-21 15:45:41Z

Your query text is mangled enough that I can't even figure out what the intention of it is. You are joining 3 things, one of which seems to be a alias to a column, not a table.

You cannot use select-list aliases in your join condition. You need to refer to the columns/expressions explicitly.

ON table1.text_column = table2.json_column->>'thing'

Based on your edit, I think I can see the intention there.

You are joining to matching_ch_acc_software twice, but apparently without meaning to. You are joining it to itself with INNER JOIN, and to the other table with ,. Since explicit joins are tighter than commas, at the point it is evaluating the inner join the invoices table is not yet "available", which is why you get the error message you do. Also, once you assign an alias to a table, you must reference columns with that alias, not with the original table name.

Finally, (invoices.data -> 'customer_name')::text means the same thing as invoices.data->>'customer_name', but is uglier.

SELECT inv.data -> 'total_amount' as iTotalAmount, 
   inv.data -> 'due_date' as iDueDate,
   inv.data -> 'emission_date' as iEmissionDate,
   inv.data -> 'customer_name' as iCustomerName, 
   matc.ch_number as mCompanyHouseNumber,
   matc.acc_software_company_name as mAccSoftName
   FROM invoices inv INNER JOIN matching_ch_acc_software matc
   ON inv.data ->> 'customer_name' =  matc.acc_software_company_name
   WHERE inv.rating_data IS NOT null ;

Collectives™ on Stack Overflow

Postgres: JOIN between JSON value and TEXT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related