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

Postgresql – SQL query with JSON and WHERE clause

I have a postgreSQL table that contains the following:

    select * from ci_grp;

      ci    |                             grp
------------+-------------------------------------------------------------------------------------------
 Ci1        | [{"Name":"TEAM.Virtu"},{"Name":"TECH.LinuxDevice"}]
 Ci2        | [{"Name":"TECH.LinuxDevice"},{"Name":"TEAM.Monitoring"}]
 Ci3        | [{"Name":"TEAM.Noc"},{"Name":"TEAM.Virtu"},{"Name":"TECH.DellEsx"},{"Name":"TECH.SNMP"}]
 Ci4        | [{"Name":"TEAM.Monitoring"},{"Name":"TECH.Postgresql"}]
 Ci5        | [{"Name":"TEAM.Monitoring"},{"Name":"TECH.LinuxDevice"},{"Name":"TECH.ZabbixProxy"}]

I need to get a reply that would only returns the CI names where ‘grp’ is both {"Name":"TECH.LinuxDevice"} and {"Name":"TEAM.Monitoring"} ==> Thus, Ci2 and Ci5. What would be the best SQL query to get this?

I’ve first tried:

WITH
    GetGrps AS (SELECT ci, json_array_elements("grp")->>'Name' AS grp from ci_grp)
SELECT GetGrps.ci, GetGrps.grp
FROM GetGrps
WHERE GetGrps.grp LIKE '%TECH.Monitoring%';

The pb with that query is I that I get 1 record per Key/Value pair:

     ci        |          grp
---------------+------------------------
 Ci1           | TEAM.Virtu
 Ci1           | TECH.LinuxDevice
 Ci2           | TECH.LinuxDevice
 Ci2           | TEAM.Monitoring
 Ci3           | TEAM.Noc
 Ci3           | TEAM.Virtu
 Ci3           | TECH.DellEsx
 Ci3           | TEAM.SNMP
 Ci4           | TEAM.Monitoring
 Ci4           | TECH.Postgresql
 Ci5           | TEAM.Monitoring
 Ci5           | TECH.LinuxDevice
 Ci5           | TECH.ZabbixProxy

Thus of course, when I try to add

AND GetGrps.grp LIKE '%TECH.LinuxDevice%'

I get nothing! I even try to concatenate the different groups in a string (with CONCAT or with string_agg), but I could not make it.

Tags:

2

Answers


  1. 0

    First create an additional array column names that contains the "Name" attributes of the JSON array (t CTE) and then select those records from t whose names attribute contains '{TECH.LinuxDevice, TEAM.Monitoring}' array using @> operator.

    with t as
(
  select cg.*, array_agg(j ->> 'Name') as names
  from ci_grp cg
  cross join lateral jsonb_array_elements (grp) as j
  group by ci, grp
)
select ci, grp
from t
where names @> '{TECH.LinuxDevice,TEAM.Monitoring}';

    DB-Fiddle demo.

    You may also try a normalized data desidn with the "Name" attribute (only one per record) in a separate table. That would make things much cleaner and easier.

    Login or Signup to reply.
  2. 0

    If you switch your json to jsonb, you can use the @> containment operator directly:
    demo at db<>fiddle

    select ci, grp from ci_grp
where grp::jsonb @> '[{"Name":"TEAM.Monitoring"},{"Name":"TECH.LinuxDevice"}]';
    ci grp
    Ci2 [{"Name": "TECH.LinuxDevice"}, {"Name": "TEAM.Monitoring"}]
    Ci5 [{"Name": "TEAM.Monitoring"}, {"Name": "TECH.LinuxDevice"}, {"Name": "TECH.ZabbixProxy"}]

    To make things fast, you can set up an expression index that indexes your json values as jsonb:

    create index on ci_grp using gin ((grp::jsonb));

    Or you can change the type permanently, then build a regular, non-expression GIN index on that:

    alter table ci_grp alter column grp type jsonb using (grp::jsonb);
create index on ci_grp using gin (grp);

    Storing your data as jsonb saves space, speeds things up, simplifies indexing and enables way more functions and operators. Note that there’s a slight difference between the types:

    Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

    Whitespace in key names and their values are considered significant and so is array order, so those won’t be affected.

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

Back To Top
Search