Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sqUUL-00A8om-MV for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Sep 2024 09:27:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sqUUL-0086FI-3Q for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Sep 2024 09:27:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sqUUK-0086FA-LA for pgsql-hackers@lists.postgresql.org; Tue, 17 Sep 2024 09:27:12 +0000 Received: from mail-wm1-x329.google.com ([2a00:1450:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sqUUI-001cGg-0W for pgsql-hackers@lists.postgresql.org; Tue, 17 Sep 2024 09:27:11 +0000 Received: by mail-wm1-x329.google.com with SMTP id 5b1f17b1804b1-42cb1758e41so34351445e9.1 for ; Tue, 17 Sep 2024 02:27:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726565228; x=1727170028; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=fxcs6zvOL8PYQQA9Bf3hXSwq0oBxuXsaMzJ8P3AUYrQ=; b=lp1cOHeBQnQx0xFgpCtHeIQAX+z6FBbaTrgHCUWVwYQz9WyawS3c9UD16EzNqApoYx qRcNfrljqLNBRGk0WhBr94cRf9pplmLUTJlBsCdLGopPrKzuLceAySnls7JwArgh3FkZ DfIbtCw61m59vm8oBdKZ1SUAi+/EazYei3rhJVybBKh6up2X4fII9qWhfwIdrJiG1jFS yE4Ti+38CPBoSmSEBVphyE3tB58G+vZ3tdwj6JBTRIJQQ3QIymz/1+MWM6ivyEEKEjmW cjMA3DoMY9QlPkbiZtfaaSzxF07rIGU6UAq/pWfGdeZf/PvjZT7ddMEA61pRwc/CxO/c FgKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726565228; x=1727170028; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=fxcs6zvOL8PYQQA9Bf3hXSwq0oBxuXsaMzJ8P3AUYrQ=; b=HPdOiXCNhjoQ4PMxCYm/DVZwrrTOudlDsM+AtVI6snLqbIojFXraJ4NTkjIt4e5Ri9 ma5V6AnnAkmkyh5wegnTR67+b4M6T8QOsDNrYv1a0DG6HF/DWZsvTP61OCyYcAgjEFrz W+xm3y6DiHbwSogYN5vJg2Fajc1hDYpkxxehreteak/AyYO36JhfgwcHGP91FwLAJ++h PGq2oOaWa8j1qBlrRM5aDtDrSCBtayKWp50chE8lYujGiyuWemdVpagQGmmTtRCb7KWa gjnUuEACtZ+ws/khN4da7sPFen0Y9OjndP5SkwdULmUpLxFKPqWnmWlfBtxYsND8oAZc Dh/A== X-Gm-Message-State: AOJu0YxZiwGo9THhpG4RUOQpAD4UfRIGG9SgHly+xsy1UC6yChP1dGYf 0omp+TkiVYHpoSW/OBRXw2+LziwwcnAgbRdfLJjfCUsfu9GXxMPgpfy9uco3jRw= X-Google-Smtp-Source: AGHT+IE4s32Z0VxbYSxTENilxgSNU9WRoTJuADLF83BoGVYh+GOvfPVsOX3o7P7TDmYQWxH7tcsBiQ== X-Received: by 2002:adf:cd8b:0:b0:374:b5fc:68df with SMTP id ffacd0b85a97d-378d61d4f96mr7821995f8f.4.1726565227750; Tue, 17 Sep 2024 02:27:07 -0700 (PDT) Received: from smtpclient.apple (adsl-192.176.58.225.tellas.gr. [176.58.225.192]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-378e780016asm9092999f8f.85.2024.09.17.02.27.06 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 17 Sep 2024 02:27:07 -0700 (PDT) From: Florents Tselai Content-Type: multipart/alternative; boundary="Apple-Mail=_960131EC-BA76-4C10-8CEF-6EAC722F799F" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\)) Subject: jsonb_strip_nulls with arrays? Message-Id: <4BCECCD5-4F40-4313-9E98-9E16BEB0B01D@gmail.com> Date: Tue, 17 Sep 2024 12:26:36 +0300 To: PostgreSQL Hackers X-Mailer: Apple Mail (2.3776.700.51) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_960131EC-BA76-4C10-8CEF-6EAC722F799F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Currently:=20 jsonb_strip_nulls ( jsonb ) =E2=86=92 jsonb Deletes all object fields that have null values from the given JSON = value, recursively. Null values that are not object fields are = untouched. > Null values that are not object fields are untouched.=20 Can we revisit this and make it work with arrays, too? Tbh, at first sight that looked like the expected behavior for me. That is strip nulls from arrays as well. This has been available since 9.5 and iiuc predates lots of the jsonb = array work. In practice, though, whenever jsonb_build_array is used (especially with = jsonpath), a few nulls do appear in the resulting array most of the times, Currently, there=E2=80=99s no expressive way to remove this. =20 We could also have jsonb_array_strip_nulls(jsonb) as well --Apple-Mail=_960131EC-BA76-4C10-8CEF-6EAC722F799F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

Currently: 


jsonb_strip_nulls ( jsonb ) =E2=86=92 jsonb

Deletes all object = fields that have null values from the given JSON value, recursively. = Null values that are not object fields are untouched.


> Null values that are not object = fields are untouched. 


Can we revisit this and make it work with arrays, = too?

Tbh, at first sight that looked like the expected = behavior for me.

That is strip nulls from arrays as well.


This has been available since 9.5 and iiuc predates lots of the = jsonb array work.


In practice, though, = whenever jsonb_build_array is used (especially with = jsonpath),

a few nulls do appear in the resulting array most of the = times,

Currently, there=E2=80=99s no expressive way to remove this. =  


We could also have = jsonb_array_strip_nulls(jsonb) as well


= --Apple-Mail=_960131EC-BA76-4C10-8CEF-6EAC722F799F--