In plpsql, I'm trying to return response as JSON

IF (skip = 0) THEN
    SELECT COUNT(*) as totalcount
    FROM table1 c
    INNER JOIN table2 n ON n.chapter = c.id
    WHERE c.revision = revisionId
        AND n.data IS NOT NULL
        AND n.type = 'text'
        AND data ILIKE query INTO totalcount;
  END IF;

  response.totalcount := totalcount;

How do I return json, like here:

{ totalcount: 5, skip: 52, result: [{data: "book1", type: chemistry}, {data: "book2", type: physics}]}

1

Answers


  1. Laurenz Albe
    0 Votes

    Try to wrap it in an outer query and use to_json:

    SELECT to_json(wrap)
    FROM (SELECT ...) wrap;
    
Please signup or login to answer this question.

Signup for an account and start participating in our site today!




Social Signup

You can also login with your Facebook, Twitter or Linkedin account

Log in with Facebook