1

version is PostgreSQL 14.2 I have perfect sql query:

select  
        details ->> 'id' as vr_id, 
        details ->> 'lastname' as lastname, 
        details ->> 'birthdate' as birthday, 
        jsonb_path_query_first(details, 
                              '$.contacts.phoneList[*] ? (@.fullNumber == "0671234567" && @.verifyStateId == 1)'
                             ) ->> 'businessId' as businessId,
        jsonb_path_query_first(details, 
                              '$.contacts.phoneList[*] ? (@.fullNumber == "0671234567" && @.verifyStateId == 1)'
                             ) ->> 'fullNumber' as fullnumber
from public.information
where details @? '$.contacts.phoneList[*] ? (@.fullNumber == "0671234567" && @.verifyStateId == 1)';

I need use in this SQL the parameters for phone number and stateId.

vPhone varchar := '0671234567'; -- fullNumber

vStateId int := 1; -- verifyStateId

something like that

select  
        details ->> 'id' as vr_id, 
        details ->> 'lastname' as lastname, 
        details ->> 'birthdate' as birthday, 
        jsonb_path_query_first(details, 
                              '$.contacts.phoneList[*] ? (@.fullNumber == "vPhone" && @.verifyStateId == vStateId)'
                             ) ->> 'businessId' as businessId,
        jsonb_path_query_first(details, 
                              '$.contacts.phoneList[*] ? (@.fullNumber == "vPhone" && @.verifyStateId == vStateId)'
                             ) ->> 'fullNumber' as fullnumber
from public.information
where details @? '$.contacts.phoneList[*] ? (@.fullNumber == "vPhone" && @.verifyStateId == vStateId)';

I plan use this sql as procedure for .Net Core API.

How I can to give these parameters to sql-query?

Improve this question
2
Related questions
0
How to execute this SQL query using ADO.NET?
4
Parameterize SQL query
0
Add parameter to command ADO.NET
Load 7 more related questions Show fewer related questions

0

Reset to default

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.