Parameter in JSON path query

Ignition
1

Hi there!
I would like to use a json path query in a named query, but I can't figure out how to use the named query paramters in the path query.

My query looks like this:

SELECT jsonb_path_query(payload->'Parameters', '$.*[*] ? (@.index == $i)', '{"i":110}') -> 'value' as value FROM table

Ideally I want to replace the index 110 with a named query parameter. I can not set the parameter because the path query is a string. I tried replacing '{"i":110}' with CONCAT('{"ind":','110','}'), that does not work.

I can make it work with system.db.runPrepQuery in a script, but it would be nice to use a named query.

Would appreciate any ideas how to make this work.

2

Use:

concat('{"i":', :someParam, '110}')
3

Thanks for the reply. I think my description was not the best. I try to replace the index number (e.g. 110) with a parameter. So I was expecting this to work:

select  jsonb_path_query(payload->'Parameters', '$.*[*] ? (@.index == $i)',concat('{"i":',:someParam,'}') ) -> 'value' as value
FROM table

... it does not: I get this error:

GatewayException: org.postgresql.util.PSQLException: ERROR: function jsonb_path_query(jsonb, unknown, text) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 9
	caused by Exception: org.postgresql.util.PSQLException: ERROR: function jsonb_path_query(jsonb, unknown, text) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 9
4

That means the parameter is in fact being passed, but the DB doesn't think a string is appropriate for that function. I'm not familiar enough with that function to advise further.

1 Like
5

Thanks @pturmel. after reading your comment it clicked.

This works:

select  jsonb_path_query(payload->'Parameters','$.*[*] ? (@.index == $ind)', concat('{"ind":',:someParam,'}')::jsonb ) -> 'value' as value
FROM table

The function expects an jsonb object insted of a string.

1 Like