Custom Query Builder - possible to use parameters for input values (within JSON)

Wappler General How To
#1

Hi all,

Started working with Custom Queries today.

I’d like to know if it’s possible, and if it is the correct syntax to input a parameter into the schema I am inputting into a JSONB column in postgres.

Example input query:

INSERT INTO cars(cars_info)
VALUES('{"brand": "Toyota", "color": ["red", "black"], "price": 285000, "sold": true}');

Ideally, what I would want to be able to do is replace ‘Toyota’ with :P1 and the expression will come from a previous step.

So far, I’ve tried:

INSERT INTO cars(cars_info)
VALUES('{"brand": ":P1", "color": ["red", "black"], "price": 285000, "sold": true}');

and

INSERT INTO cars(cars_info)
VALUES('{"brand": "(:P1)", "color": ["red", "black"], "price": 285000, "sold": true}');

But the value being entered into the db is :p1 so I’m wondering if this is even possible with Wappler and if I’m just doing it incorrectly, currently…

#2

Maybe try to create the JSON using a set value step and then just use that as :P1 parameter’s value.

#3
#4

Thanks for the reply @sid and the alternative- do you believe this to not be possible the way I’m trying to do it currently?

#5

From what I understand, its the double & single quotes that might be causing some issue in your approach.
All my use cases with JSON in past have always had JSON already built when inserting. Never had to create at the time of insert query.

I see George has assigned Patrick, so he should have the exact answer.

1 Like
#6

Thanks Sid. I’ll try playing around with the double and single quotes to see if I can make any progress.

#7

Hi @patrick, any chance for some expertise here on if this is possible and the correct syntax?

#8

Little bump @patrick - have held off working on this part again, in anticipation of a response. Can you let me know if this is indeed possible, and what the correct syntax should be?

#9

You can use only use parameters for values, you could put the json with a setValue step first and then replace the :P1 with the value you need and use the whole json as parameter.

#10

Ok, great - thanks for the clarification. That’ll save time trying to make it work the other way!

Follow up question then re: setValue step. If I choose the API output, it has say 10 fields. Whenever I try to use a setValue > create schema with only 5 of the 10 fields, the setValue still outputs the full API response, not only the 5 fields created manually in the schema.

I’m guessing this is also expected, and as it should be? So i’ll need to restrict output another way?

#11

Make use of the new filter columns component for selective data to be set in set value.

#12

Thanks @sid. I would be the initial data source from the API is an object, and thus won’t work at this point with data transformations.

I’ll figure something out! Thank you both for your help.