0

I'm trying to create a SQL function which is collecting JSON data from the db . First i faced the problem that i cant write function(JSON_EXTRACT,...) but after little research i think when i remove the "function" it works . But now when i try to put the values to List it throws this error . My Function :

create function returnRecord()
    returns boolean
    deterministic
begin
    declare @myList varchar(255);

    SELECT r.data as r1
    INTO @myList
    FROM records r
             join columns c on r.view_id = c.view_id
    where ('JSON_EXTRACT', r.data, c.name);
end;

I was trying different ways and i want to take the Json data from the db .

Martin Bozhinov
  • 1
  • 2
  • If you remove the "into" line from your query, can this query be successfully executed as simple select outside any function or is even this query already invalid? – Jonas Metzler Dec 05 '22 at 09:04
  • `@Query(value = "SELECT r FROM Record r join r.view v where function('JSON_EXTRACT',r.data, concat('$.',:column)) = :value " + "and v.name = :view")` that's my original JPQL query , but im trying to do this query into function because i have to use the result and iterate through it .. – Martin Bozhinov Dec 05 '22 at 09:06
  • Sorry, please forget my question, I misunderstood your intention. – Jonas Metzler Dec 05 '22 at 09:08
  • You cant declare user defined variables https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql, there is no return statement in the published function https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html and you don't appear to be setting delimiters https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html – P.Salmon Dec 05 '22 at 09:26
  • 1
    Please add representative sample data and expected outcome as text. – P.Salmon Dec 05 '22 at 09:27
  • So i have my JPQL query (above your comments) and this JPQL is returning lets assume this line `{""123"": ""1233"", ""ivan"": ""aaass"", ""pesho"": ""peshooo""}"` i have to do in the function a iteration through all the data which is saved in unique columns so i have to add the columns table and search if the column is unique and in its records if the data is present . Also i tried to set the list with the given query `set @myList = SELECT r.data FROM records r join columns c on r.view_id = c.view_id where ('JSON_EXTRACT', r.data, c.name);` – Martin Bozhinov Dec 05 '22 at 09:36

0 Answers0