La manipulation de l'Unicode des séquences dans postgresql
J'ai quelques JSON données stockées dans un JSON (pas JSONB) colonne dans ma base de données postgresql (9.4.1). Certains de ces JSON contiennent des structures de l'unicode des séquences dans leurs valeurs d'attribut. Par exemple:
{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }
Quand j'essaie de requête JSON colonne (même si je ne suis pas directement en essayant d'accéder à la device_name
attribut), j'obtiens l'erreur suivante:
ERREUR: non pris en charge séquence d'échappement Unicode
Détail:\u0000
ne peuvent être converties en texte.
Vous pouvez recréer cette erreur en exécutant la commande suivante sur un serveur postgresql:
select '{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }'::json->>'client_id'
L'erreur fait sens pour moi - il n'y a tout simplement aucun moyen de représenter la séquence unicode NULL
dans un résultat textuel.
Est-il de toute façon pour moi de m'interroger les mêmes données JSON sans avoir à effectuer un "assainissement" sur les données entrantes? Ces JSON structures de changer régulièrement de la numérisation d'un attribut spécifique (device_name
dans ce cas) ne serait pas une bonne solution car il pourrait facilement y avoir d'autres attributs qui pourrait détenir des données similaires.
Après quelques investigations, il semble que ce comportement est nouveau pour la version 9.4.1 comme mentionné dans le changelog:
...Donc
\u0000
sera désormais également être rejeté en json valeurs lors de la conversion de caractères d'échappement de forme est nécessaire. Ce changement ne se casse pas la capacité à stocker\u0000
en json colonnes aussi longtemps qu'aucun traitement n'est effectué sur les valeurs...
Était-ce vraiment l'intention? Est d'une décote de pré 9.4.1 une option viable ici?
Comme une note de côté, cette propriété est tiré du nom du client mobile de l'appareil - c'est l'utilisateur qui est entré dans ce texte dans l'appareil. Comment sur la terre n'a un utilisateur d'insérer NULL
et CARACTÈRE de REMPLACEMENT
valeurs?!
- Pour référence, ce n'est pas seulement le cas avec les
SELECT
- je obtenir le même problème avec un SQLUPDATE
déclaration sur 9.5 et 9.6. - Pour info, j'ai couru le
SELECT
que vous avez fourni sur un PostgreSQL 10.1, compilé par Visual C++ construire 1800, 32-bit et il a renvoyé la même erreur.
Vous devez vous connecter pour publier un commentaire.
\u0000
est le seul point de code Unicode qui n'est pas valide dans une chaîne de caractères. Je ne vois pas d'autre moyen que de désinfecter la chaîne.Depuis
json
est juste une chaîne de caractères dans un format spécifique, vous pouvez utiliser le standard de la chaîne de fonctions, sans se soucier de la structure JSON. Une ligne de désinfectant pour supprimer le point de code serait:Mais vous pouvez également insérer n'importe quel caractère à votre goût, ce qui serait utile si le zéro point de code est utilisé comme une forme de délimiteur.
Note aussi la subtile différence entre ce qui est stocké dans la base de données et la façon dont il est présenté à l'utilisateur. Vous pouvez stocker le point de code dans une chaîne JSON, mais vous devez vous pré-traiter à un autre personnage avant le traitement de la valeur en tant que
json
type de données."\u0000"
dans la chaîne de réponse?\\u0000
avec\\\\u0000
et vous obtiendrez "FooBar�\u0000�\x0F�" pour "nom du périphérique" (testé sur 9.4.4), mais vous auriez encore àregexp_replace
la première json.replace
au lieu de l'expression rationnelle de la version, par exempleselect replace('{ "a": "null \u0000 word \u0000 escape" }', '\u0000', '')::json->> 'a' as succeeds;
La solution par Patrick ne pas travailler hors de la boîte pour moi. Peu importe, il y a toujours une erreur renvoyé. J'ai ensuite fait des recherches un peu plus et a été en mesure d'écrire une petite fonction personnalisée qui a résolu le problème pour moi.
D'abord j'ai pu reproduire l'erreur en écrivant:
Puis j'ai ajouté une fonction personnalisée que j'ai utilisé dans ma requête:
Appeler la fonction à faire. Vous ne devriez pas recevoir un message d'erreur.
Alors que cela devrait revenir le json comme prévu:
json_array_elements
échouera. Donc les jeter ensemble json vous travaillez avec detext
, puis de remplacer et d'acteurs dejson
et alors, la requête devrait fonctionner.