Question 249287 in PostgreSQL Question posted in
The official documentation can be found here.

Query formation from JSON object in postgresql

I had a requirement like, I need to form a query based on JSONB object parameter and returns its output.

JSONB parameter contains Table name, where clause details, Column value to be returned form a table.

Below is the sample format of JSONB where getjsontesterabledata is the function which takes JSONB as parameter.

select getjsontesterabledata('{"MODULE": "TEST", 
    "TEST_INFO": {
        "AGGREGATE_OP": "", 
        "RESULT_ATTRIB_NAME": "TESTER_VALUE", 
        "CONDITIONAL_ATTRIBS": [
            {"VALUE": "98842", "ATTRIBUTE": "TEST_ID", "CONDITION_OP": "="},
            {"VALUE": "DRIVE", "ATTRIBUTE": "TEST_NAME", "CONDITION_OP": "="}]
}}');

In above JSONB param, MODULE is table name , AGGREGATE_OP is like SUM, AVG … , CONDITIONAL_ATTRIBS are like where clause.

From the above JSONB parameter, Query formation should be like

select TESTER_VALUE
from test
where TEST_ID = 98842 and TEST_NAME = 'DRIVE';

and this query output should be returned.

Here i am stuck at forming a Query from JSONB Parameter specifically in forming a where clause. I need help on this.

Please note that PostgreSQL version is 12.15.

Tags:

2

Answers


  1. 0

    Basically here it is. Pls. note the formation of the where clause and coalesce-ing the text to true if there is no data for "CONDITIONAL_ATTRIBS".
    Please also note that this looks very much like a SQL injection nightmare and only makes sense if the input parameter data do not come from user input.

    create or replace function getjsontesterabledata(j jsonb)
returns text language sql as  
$$
select
'select ' || (j -> 'TEST_INFO' ->> 'RESULT_ATTRIB_NAME') || '
 from '  || (j ->> 'MODULE') || '
 where ' || coalesce (
 (
   select string_agg((ca ->> 'ATTRIBUTE') || (ca ->> 'CONDITION_OP') || '''' || (ca ->> 'VALUE') || '''', ' and ')
   from jsonb_array_elements(j -> 'TEST_INFO' -> 'CONDITIONAL_ATTRIBS') ca
 ), 'true') 
$$;


select getjsontesterabledata('{"MODULE": "TEST", 
    "TEST_INFO": {
        "AGGREGATE_OP": "", 
        "RESULT_ATTRIB_NAME": "TESTER_VALUE", 
        "CONDITIONAL_ATTRIBS": [
            {"VALUE": "98842", "ATTRIBUTE": "TEST_ID", "CONDITION_OP": "="},
            {"VALUE": "DRIVE", "ATTRIBUTE": "TEST_NAME", "CONDITION_OP": "="}]
}}'::jsonb);

    The result is

    select TESTER_VALUE
 from TEST
 where TEST_ID='98842' and TEST_NAME='DRIVE'
    Login or Signup to reply.
  2. 0

    You can make a loop using jsonb_array_elements(data_json->'TEST_INFO'->'CONDITIONAL_ATTRIBS').

    jsonb_array_elements expands the top-level JSON array into a set of
    JSON values.

    -- FUNCTION: public.getjsontesterabledata(text)

-- DROP FUNCTION IF EXISTS public.getjsontesterabledata(text);

CREATE OR REPLACE FUNCTION public.getjsontesterabledata(jsontext text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    query_text text;
    w record;
    data_json jsonb;
    count_loop int;
BEGIN
    query_text := '';
    count_loop:= 1;
    data_json := jsontext::jsonb;       
    query_text := concat('select ', data_json->'TEST_INFO'->>'RESULT_ATTRIB_NAME', ' from ', data_json->>'MODULE');
    
    for w in       
           select jsonb_array_elements(data_json->'TEST_INFO'->'CONDITIONAL_ATTRIBS') as col
    loop 
        if count_loop = 1 then
            query_text := concat(query_text, ' where ', w.col->>'ATTRIBUTE', w.col->>'CONDITION_OP', '''', w.col->>'VALUE', '''');
        else
            query_text := concat(query_text, ' and ', w.col->>'ATTRIBUTE', w.col->>'CONDITION_OP', '''', w.col->>'VALUE', '''');
        end if;
        
        count_loop:= (count_loop + 1);
    end loop;
RETURN query_text;
END
$BODY$;

select getjsontesterabledata('{"MODULE": "TEST", 
    "TEST_INFO": {
        "AGGREGATE_OP": "", 
        "RESULT_ATTRIB_NAME": "TESTER_VALUE", 
        "CONDITIONAL_ATTRIBS": [
            {"VALUE": "98842", "ATTRIBUTE": "TEST_ID", "CONDITION_OP": "="},
            {"VALUE": "DRIVE", "ATTRIBUTE": "TEST_NAME", "CONDITION_OP": "="}]
}}');

    You can see this function working in dbfiddle.

    Some resources used:

    Postgresql looping through query results

    Using a different type of FOR loop, you can iterate through the
    results of a query and manipulate that data accordingly.

    [ <<label>> ] FOR target IN query LOOP
     statements END LOOP [ label ];

    Postgresql json functions documentation

    json -> text → json

    jsonb -> text → jsonb

    Extracts JSON object field with the given key.

    ‘{"a": {"b":"foo"}}’::json -> ‘a’ → {"b":"foo"}

    json ->> text → text

    jsonb ->> text → text

    Extracts JSON object field with the given key, as text.

    ‘{"a":1,"b":2}’::json ->> ‘b’ → 2

    Postgresql create function documentation

    Postgresql concat documentation

    concat ( val1 "any" [, val2 "any" [, …] ] ) → text

    Concatenates the text representations of all the arguments. NULL
    arguments are ignored.

    concat(‘abcde’, 2, NULL, 22) → abcde222

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search