How do I select json from my table Postgres

Question

I have a table named loans with a lot of fields such as (id, name, description, etc.) And inside that table, I have a column which type is JSON(TermsMessage)(ARRAY OF JSON), and when I make a select query it returns me an error

my query

`

  select 
    z.loan_id, 
    z.loan_name, 
    z.max_amount, 
    z.min_amount,
        z.min_age,
        z.max_age,
        z.min_loan_term,
        z.max_loan_term,
        z.TermsMessage,
    to_json(array_agg(distinct z.jsjs)) as aims,
    to_json(array_agg(distinct z.jsjs1)) as cities,
    from(
        select 
        c.loan_id, 
        c.loan_name, 
        c.max_amount,
        c.min_amount,
        c.min_age,
        c.max_age,
        c.min_loan_term,
        c.max_loan_term,
        c.TermsMessage,
        ( select x from (select c.aim_id, c.aim_name) x) as jsjs,
        ( select x from (select c.city_id, c.city_name) x) as jsjs1
        from 
        ( select 
        l.id as loan_id, 
        l.name as loan_name, 
        l.max_amount, 
        l.min_amount,
        l.min_age,
        l.max_age,
        l.min_loan_term,
        l.max_loan_term,
        l.TermsMessage,
        --
        a.id as aim_id, 
        a.name as aim_name,
        --
        c.id as city_id,
        c.name as city_name
        --
        from loans as l
        left join loan_aims as la on la._loan = l.id
        left join aims as a on a.id = la._aim
        ------------
        left join loan_cities as lc on lc._loan=l.id
        left join cities as c on c.id = lc._city
        ------------

        ) c     
    ) z
    group by z.loan_id,
        z.loan_name, 
        z.max_amount,
        z.min_amount,
        z.min_age,
        z.max_age,
        z.min_loan_term,
        z.max_loan_term;

`

termsMessage must appear in the GROUP BY clause or be used in an aggregate function

and when I add json column into GROUP BY it return an another error

 group by z.loan_id,
            z.loan_name, 
            z.max_amount,
            z.min_amount,
            z.min_age,
            z.max_age,
            z.min_loan_term,
            z.max_loan_term,
            z.TermsMessage;

could not identify an equality operator for type json

George S · Accepted Answer · 2018-12-08 05:14:04Z

1

PostgreSQL doesn't have a built-in equality operator for the datatypes JSON or arrays of JSON. When grouping by a JSON field, the db needs to know which are the same values and can be grouped together. You can work around this by turning the JSON (or JSON arrays) into either text arrays or JSONb arrays using something like ::jsonb[] or ::text[]. I'd probably do in the innermost part of the query so that it flows through later naturally:

  select 
    z.loan_id, 
    z.loan_name, 
    z.max_amount, 
    z.min_amount,
        z.min_age,
        z.max_age,
        z.min_loan_term,
        z.max_loan_term,
        z.TermsMessage,
    to_json(array_agg(distinct z.jsjs)) as aims,
    to_json(array_agg(distinct z.jsjs1)) as cities,
    from(
        select 
        c.loan_id, 
        c.loan_name, 
        c.max_amount,
        c.min_amount,
        c.min_age,
        c.max_age,
        c.min_loan_term,
        c.max_loan_term,
        c.TermsMessage,
        ( select x from (select c.aim_id, c.aim_name) x) as jsjs,
        ( select x from (select c.city_id, c.city_name) x) as jsjs1
        from 
        ( select 
        l.id as loan_id, 
        l.name as loan_name, 
        l.max_amount, 
        l.min_amount,
        l.min_age,
        l.max_age,
        l.min_loan_term,
        l.max_loan_term,
        l.TermsMessage::jsonb[] as TermsMessage ,
        --
        a.id as aim_id, 
        a.name as aim_name,
        --
        c.id as city_id,
        c.name as city_name
        --
        from loans as l
        left join loan_aims as la on la._loan = l.id
        left join aims as a on a.id = la._aim
        ------------
        left join loan_cities as lc on lc._loan=l.id
        left join cities as c on c.id = lc._city
        ------------

        ) c     
    ) z
    group by z.loan_id,
        z.loan_name, 
        z.max_amount,
        z.min_amount,
        z.min_age,
        z.max_age,
        z.min_loan_term,
        z.max_loan_term,
        z.TermsMessage;

answered Dec 8, 2018 at 5:14

George S

2,19114 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George S Over a year ago

Sure. Fun background (as it originally seemed crazy to me that there was no JSON equality operator built in): PostgreSQL stores JSON to maintain the order of keys added to it, while JSONB does not. For your use case it probably does not matter, but you can actually get different results on the equality test depending on if you convert to JSONB or to text (since text will also maintain the difference in key order). With that, it makes sense that you'd need to convert one way or the other to compare equality.

Collectives™ on Stack Overflow

How do I select json from my table Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related