JSON question

From: Dave Ekhaus <dave(dot)ekhaus(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: JSON question
Date: 2022-04-05 04:45:06
Message-ID: 1362aceb-cc4a-45f4-b57d-0b56b878ca83@Canary
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All

I'm experimenting with PostgreSQL's JSON features and have a question.

Say we have the following JSON (which will be stored in a table named 'resources' - in its 'schema' column ...

{
"name": {
"type": "string",
"label": "Name",
"accessor": "name",
"is_association": false
},
"books": {
"type": "array",
"label": "Books",
"accessor": "books",
"is_association": true
},
"email": {
"type": "string",
"label": "Email",
"accessor": "email",
"is_association": false
},
"date_of_birth": {
"type": "date",
"label": "Birthday",
"accessor": "date_of_birth",
"is_association": false
}
}

[NOTE: the JSON above is the 'schema' attribute of one record in the 'resources' table. There will be many rows in the 'resources' table]

How can I query the 'resources' table to return the rows in which there's at least one JSON object whose 'type' equals 'array' ?

Thanks
Dave

Browse pgsql-general by date

  From Date Subject
Next Message J. Roeleveld 2022-04-05 05:37:23 Re: Select .... where id not in (....) returns 0 incorrectly
Previous Message BeginnerC 2022-04-04 23:34:39 Where is the best place to read for the paper around database?