0

I would like to execute an Insert / Update operation in a table in a postgresql database. The table has several columns, including a 'details' column of type jsonb which is the target for most of my data.

All would be well except that the key id to match on lives within the details object. Postgres has the '->>' operator to get keys from a json object. Ergo I can query like this:

select * from customers where details ->> 'myId' = '1234'

Does this exist in Pentaho? When I open up the Insert / Update interface I can only select column names under 'table field', ideally I would like to write "details ->> 'myId'".

Is this possible, or do I have to write my own class? I am not sure how to approach it. Can I get a dump of the Insert / Update class and then modify it?

Thanks a lot for any help

Pim
  • 71
  • 4

1 Answers1

0

SQL json query syntax it´s now allowed by all the sql dialects, so this is a thing you will have to accomplish from a straight sql query. First do your select with json operator from a table input step which allows you to build your query with the json sql operators

select * from customers where details ->> 'myId' = '1234'

and pass the rows to the next step and after that use a database join step

insert into my_table (field1,field2,field3...) values ('?','?','?'...);

and select the parameter fieldname from previous steps and use ? placeholders in your query.

If you want to do a upsertion (insert/update) it depends of the sql engine/dialect you are using, in postgres you can build a upsertion query as follows in this post:

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Hope it helps. Welcome to s.o. :D

jacktrade
  • 3,125
  • 2
  • 36
  • 50