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 1sA3Qq-007Evp-P2 for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 08:04: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 1sA3Qq-009eMd-DE for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 08:04:12 +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 1sA3Qq-009eMU-20 for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 08:04:12 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sA3Qn-001aV7-1J for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 08:04:10 +0000 Received: by mail-ej1-x62a.google.com with SMTP id a640c23a62f3a-a59c0a6415fso1146663166b.1 for ; Thu, 23 May 2024 01:04:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1716451447; x=1717056247; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to; bh=i5dNwf0KNE3BYvAn9LmR/OP0nZKjlHo0v3XZJOvsXMQ=; b=ccoGvDD5GrgB84XD8mvD2RBRIPd3LTrMLrkKdRR3Aqvf4I230t6ClEw+1Juzm8JoA+ pRjgCewc/kDEqoxyqk+OGjiavPjVlePjbnAmWeNxuLof6WQZSgC/giFnhqqn/0Eizlxz QC2J6DzCuxUJ1dHw5gmue33/TaQIBeQnUsx7DH/SWu4c5b5fEY6w2i4wVDD47w/DBtYK UDCfvdbcQlrcrzSkOgzilNkTLySzz0Lvkv6ZEjQMaGIboQMfbW1My0WuhAUZ10NHxTbC U0PTDd0IcBf9v6odPLqSvcBj//70/VQKrNm1f0Tbb7Vpsop4DBtMfenPJYaYdKAjDojc QseQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716451447; x=1717056247; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=i5dNwf0KNE3BYvAn9LmR/OP0nZKjlHo0v3XZJOvsXMQ=; b=nx/lpAp30klJRGr161SljCtdiU3s7vzPUY6cUcUt5P6/y9zt/j67PZ9kscVTKi58On 9C3kmngkCse6TqzbvBPxim1mCw9EdKpE0KNgRZlZePcXp3m6rE4el/THo5EaVfxQOC4H +BYTPv68Z2GopR2zrQ7yqZi80a7CbOdxuf+qXUqZd839WUEAHxpTKxi6Si8szvjm22Lj NXZjYsoZOWrJZ4+m7Ww3ZFqnYTHDlmBkddoBq9KtDGOH2EpCnogNHkFWvPW/YZ6pfJy4 eUkp4vEVTvVOpBgaQA2BpZX4kwFiSm3iWT8n4yaJN141yFpzIbxHEeRfjjK4nXugdVIJ t8uA== X-Forwarded-Encrypted: i=1; AJvYcCUBmSfpVo5iUYFLAXrI6cL8HeCtoRpgyNGHY0YIytuDPNqrIkCj4fRHJSKt9HQDIPnKcg4mDQHXqNcASF48JuYLSD5kcN3BI5NvJoXWAAGqGoin X-Gm-Message-State: AOJu0Yy46rD3Fry90gr+D5SXczxKSyY983mDebKiBl9obYiBo/Mx5ujU MwyrYX4ywPhhs6Z+X8uCkwj+Cnl6e7XwnNMgckoJVCrs75HfBQ8OkrdYtEXI38Q= X-Google-Smtp-Source: AGHT+IEpcJQ9fEseGtvqBlk3H/ekInrK/06j8/8UcVLdqh4VZgXz7wgrWBydAyJV42rd8SkA05cOMQ== X-Received: by 2002:a17:906:4a12:b0:a59:a85d:31c6 with SMTP id a640c23a62f3a-a622818fc11mr291657966b.66.1716451447389; Thu, 23 May 2024 01:04:07 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:e895:ad22:deaa:5d3c:705d]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a5a17b015besm1924534066b.144.2024.05.23.01.04.06 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 23 May 2024 01:04:07 -0700 (PDT) Message-ID: Subject: Re: Json table/column design question From: Laurenz Albe To: Skorpeo Skorpeo , pgsql-general@lists.postgresql.org Date: Thu, 23 May 2024 10:04:06 +0200 In-Reply-To: References: Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2024-05-22 at 22:38 -0500, Skorpeo Skorpeo wrote: > I was wondering if having unrelated columns in a table is a sound approac= h when > using json.=C2=A0 In other words, if I have two collections of unrelated = json objects, > for example "Users" and "Inventory", would it be ok to have one table wit= h a > "Users" column and a "Inventory" column?=C2=A0 My concern is that from a = row > perspective the columns could be different lengths, such as more inventor= y > items as users.=C2=A0 And for any given row the data in one column would = have no > relation to another column. I would only query a single column at a time. >=20 > Would this approach be ok or are there pitfalls such that it would be > advantageous/recommended to have a separate table for each column? It doesn't matter much if you use one or two columns. But the word "collection" makes me worry. Perhaps this article can give you some ideas: https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/ Yours, Laurenz Albe