Comment transformer un tableau json en lignes dans postgres
j'ai un tableau json stocké dans ma base de données postgres. Le json ressemble à ceci:
[
{
"operation": "U",
"taxCode": "1000",
"description": "iva description",
"tax": "12"
},
{
"operation": "U",
"taxCode": "1001",
"description": "iva description",
"tax": "12"
},
{
"operation": "U",
"taxCode": "1002",
"description": "iva description",
"tax": "12"
}
]
maintenant je dois sélectionner le tableau de sorte que n'importe quel élément soit dans une ligne différente du résultat de la requête. Ainsi, L'instruction SELECT I perform doit retourner les données de cette façon:
data
--------------------------------------------------------------------------------------
{ "operation": "U", "taxCode": "1000", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1001", "description": "iva description", "tax":"12"}
{ "operation": "U", "taxCode": "1002", "description": "iva description", "tax":"12"}
j'ai essayé d'utiliser le unnest() function
SELECT unnest(json_data::json)
FROM my_table
mais il n'accepte pas l' jsonb tapez
3 réponses
je poste la réponse écrite à l'origine par pozs dans la section commentaire.
unnest() est pour les types de tableaux de PostgreSQL.
a la place, une des fonctions suivantes peut être utilisée:
json_array_elements(json)(9.3+)jsonb_array_elements(jsonb)(9.4+)json[b]_array_elements_text(json[b])(9.4+)
Exemple:
select * from json_array_elements('[1,true, [2,false]]')
valeur de sortie
-------------
| 1 |
-------------
| true |
-------------
| [2,false] |
-------------
Ici où est la documentation pour v9.4 être trouvé.
je suggère d'utiliser la commande json_to_recordset dans votre cas. Votre SQL devrait alors être:
select * from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]') as x("operation" text, "taxCode" int);
La sortie est:
------------------------
| |operation|taxCode |
------------------------
| 1 | "U" | 1000 |
------------------------
| 2 | "U" | 10001 |
------------------------
les colonnes (ou les touches JSON) de l'exemple peuvent être librement élargies.
exemple plus difficile:
supposons que vous ayez une table avec des lignes contenant chacune un tableau jsonb et que vous souhaitiez étaler (ou unnest) tous ces tableaux et faire quelques calculs agrégés sur les enregistrements qu'ils contiennent.
Table (que ce soit categories):
id | specifics (jsonb)
-----------------------------------------------------------------------------------
1 | [{"name": "Brand", "required": true}, {"name": "Color", "required": false}]
2 | [{"name": "Brand", "required": false}, {"name": "Color", "required": false}]
Donc, si vous voulez compter, comment les nombreuses spécificités qui vous avez, vous aurez besoin d'utiliser cette requête:
SELECT specs.name, COUNT(*) AS total
FROM
categories,
jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)
WHERE
specs.required = TRUE
-- AND any other restrictions you need
GROUP BY specs.name
ORDER BY total DESC;
Ici FROM x, function(x.column) est une forme raccourcie d'un jointure latérale qui rejoint effectivement chaque rang de categories avec la table virtuelle créée par jsonb_to_recordset fonction du tableau jsonb dans la même rangée.