Postgresql & jsonb : WHERE <a specific nested field in my json> IS NOT NULL

Question

Table public.challenge, column lines JSONB

My initial JSON in lines :

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc"
      },
      {
        "size": 100,
        "name": "def"
      },
      {
        "size": 100,
        "name": "ghi"
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz"
      }
    ]
  }
]

Desired result (add a new object wrapper for every bloc) :

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc",
        "wrapper": {
          "nestedName": "abc",
          "type": "regular"
        }
      },
      {
        "size": 100,
        "name": "def",
        "wrapper": {
          "nestedName": "def",
          "type": "regular"
        }
      },
      {
        "size": 100,
        "name": "ghi",
        "wrapper": {
          "nestedName": "ghi",
          "type": "regular"
        }
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz",
        "wrapper": {
          "nestedName": "xyz",
          "type": "regular"
        }
      }
    ]
  }
]

I have the following query (from here) :

WITH cte AS (
   SELECT id_lines,
          jsonb_agg(
                  jsonb_set(val1, '{blocs}',
                      (
                        SELECT jsonb_agg(arr2 ||
                                         json_build_object(
                                             'wrapper', json_build_object('nestedName', arr2->'name', 'type', 'regular')
                                             )::jsonb
                            )
                        FROM jsonb_array_elements(arr1.val1->'blocs') arr2
                        WHERE arr2->'name' IS NOT NULL
                          )
                      ))
   FROM public.challenge, jsonb_array_elements(lines) arr1(val1)
   GROUP BY 1
)
UPDATE public.challenge SET lines=(cte.jsonb_agg) FROM cte
WHERE public.challenge.id_lines=cte.id_lines;

The condition WHERE arr2->'name' IS NOT NULL does not filter out blocs where name is null, I struggle to find out why.. thanks!

Laurenz Albe · Accepted Answer · 2021-06-17 09:13:55Z

3

You have to distinguish between SQL NULL and JSON null.

The IS NOT NULL predicate tests for SQL NULL, which would mean that the attribute is not present in the JSON.

To test for JSON null, use

WHERE arr2->'name' <> 'null'::jsonb

The type cast to jsonb is not necessary and would be performed implicitly.

answered Jun 17, 2021 at 9:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgresql & jsonb : WHERE <a specific nested field in my json> IS NOT NULL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related