JSON- und SQL-Prozess: Funktionen, Beratung und Bereitstellung

Startseite » Python » JSON- und SQL-Prozess: Funktionen, Beratung und Bereitstellung

Die Arbeit mit halbstrukturierten Daten ist optional: JSON wurde in die gemeinsame Sprache für APIs, mobile Apps und Registrierungen sowie moderne SQL-Motoren konvertiert und reagierte auf native Funktionen und Design-Benutzer, um sie ohne Reibungsverluste zu integrieren. Dies ist eine praktische Anleitung, mit der ich mich beraten, umwandeln und effiziente Formulare für SQL Server, Azure SQL, PostgreSQL, Oracle und BigQuery erstellen , mit Kommentaren und Empfehlungsbeispielen vergleichen konnte.

Zusätzlich zu den Demos ist das Ziel: Erklären Sie, was Sie auf jeder Plattform tun müssen, es gibt die Unterschiede (z. B., Bediener, native Typen und Einschränkungen) und welche Indizes oder Formate können verwendet werden, um die JSON-Beratung zu erweitern . Darüber hinaus sind sie für den Import von Dateien und Dienstleistungen zuständig und migrieren Motoren mit JSON-Funktionen, die von jedem Anbieter speziell entwickelt wurden.

Das ist JSON in der Welt von SQL und muss angewendet werden

JSON ist ein beliebtes Textformat für den Austausch von Daten, die in Web-, Mobil- und Mikrodienstszenarien brillant sind. SQL Server, Azure SQL-Datenbank, Azure SQL Managed Instance, Azure Synapse Analytics und SQL-Datenbank in Microsoft Fabric enthalten Funktionen zur Analyse, Beratung und Formatierung von JSON direkt in T-SQL. Beispiel eines Dokumenttyps: [ { \“Name\“: \“John\“, \“Skills\“: [ \“SQL\“, \“C#\“, \“Azure\“ ] }, { \“Name\“: \“Jane\“, \“Nachname\“: \“Doe\“ } ].

Mit diesen Funktionen können Sie Ihre Werte erweitern , Objekte oder Arrays hinzufügen, Eigenschaften ändern, ohne das Dokument neu zu schreiben, Dateien in Dateien speichern und Ergebnisse wie eine JSON- Liste für Ihre API entwickeln. Außerdem ist die Kombination mit den Beziehungsspalten , den Inhaltszusammenfassungen und den Klauseln WHERE/ORDER BY/GROUP BY problematisch .

Neuerungen und Kompatibilität: Nvarchar-Text auf nativem JSON-Niveau

Historisch gesehen in SQL Server, wird JSON in nvarchar/varchar gespeichert und mit nativen Funktionen verarbeitet. SQL Server 2025 (17.x) führt in einer vorläufigen Version einen nativen JSON-Datentyp ein , der das Dokument im binär analysierten Format für effizientere Vorlesungen, Texte und Komprimierung schützt . Dies geschieht mit dem klassischen Modell : Die Funktionen ISJSON/JSON_VALUE/JSON_QUERY/JSON_MODIFY und OPENJSON weisen auf den empfohlenen Weg zur Beratung und Transformation des Inhalts hin.

Kompatibles Matrikel: in der vorherigen Dokumentation, dass „ JSON nicht ein integrierter Typ ist “ in öffentlichen Ausgaben; Die Einführung in den Heimattyp dauerte länger und war für alle Beteiligten noch nicht verfügbar. Daher ist nvarchar(max) der neue JSON-Typ in der Plattformfunktion und ermöglicht den Zugriff auf die JSON-Funktionen, um die Portabilität und Bereitstellung sicherzustellen.

Wesentliche Funktionen von JSON und SQL Server

Der T-SQL-Unterstützungskern kann mit folgenden Befehlen verwendet werden: ISJSON (gültig), JSON_VALUE (zusätzliche Erweiterungen), JSON_QUERY (Objekte/Arrays erstellen) und JSON_MODIFY (aktuelle Funktionen). Diese Funktionen ermöglichen das Filtern, Ordnen und Sammeln von Inhalten im JSON-Dokument, ohne dass ein Zugriff auf die Basis erforderlich ist.

Relationales+JSON-Beispiel mit Filtern und Reihenfolge für JSON-Routen: Auswahl von People-Feldern und zusätzliche Post-, Richtungs- und Skills-Codes.

SELECT Name,
       Surname,
       JSON_VALUE(jsonCol, '$.info.address.PostCode')     AS PostCode,
       JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' +
       JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
       JSON_QUERY(jsonCol, '$.info.skills')               AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
  AND Status = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Um eine konkrete Eigenschaft zu aktualisieren, muss das Dokument neu geschrieben werden: JSON_MODIFY lokalisiert die Route und unterstützt den Wert.

DECLARE @json nvarchar(max) = '{"info": {"address": [{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SELECT @json; -- {"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

De JSON a tablas con OPENJSON

Um JSON-Dateien zu löschen und mit SELECT zu konsultieren, konvertiert OPENJSON Arrays in Objekte in Dateien/Spalten und erlaubt eine explizite Eingabe mit der Klausel WITH. Geben Sie Folgendes ein: Wenn der angegebene Wert angezeigt wird , stellen Sie ihn ein und geben Sie NULL ein, wenn ein Feld verloren geht . Además puedes marcar rutas como stricto para exigir que existan.

DECLARE @json nvarchar(max) = N'[
 {"id":2, "info":{"name":"John", "surname":"Smith"}, "age":25},
 {"id":5, "info":{"name":"Jane", "surname":"Smith"}, "dob":"2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
  id         int            'strict $.id',
  firstName  nvarchar(50)   '$.info.name',
  lastName   nvarchar(50)   '$.info.surname',
  age        int,
  dateOfBirth datetime2     '$.dob'
);

Wenn das Dokument Subarrays enthält (zum Beispiel „Skills“ mit Informationen), dann öffnen Sie es mit „APPLY“, um es zu erweitern und in die Registrierung einzureichen. OUTER APPLY wiederholt den Fila des Entidads für jedes Element des Subarrays (zur Information oder Normalisierung von Teilen).

DECLARE @json nvarchar(max) = N'[
 {"id":2, "info":{"name":"John", "surname":"Smith"}, "age":25},
 {"id":5, "info":{"name":"Jane", "surname":"Smith", "skills":["SQL","C#","Azure"]},
  "dob":"2005-11-04T12:00:00"}
]';

SELECT id, firstName, lastName, age, dateOfBirth, skill
FROM OPENJSON(@json)
WITH (
  id           int           'strict $.id',
  firstName    nvarchar(50)  '$.info.name',
  lastName     nvarchar(50)  '$.info.surname',
  age          int,
  dateOfBirth  datetime2     '$.dob',
  skills       nvarchar(max) '$.info.skills' AS JSON
) AS P
OUTER APPLY OPENJSON(P.skills) WITH (skill nvarchar(32) '$');

Eine Alternative zu meiner Praxis, wenn Sie nur JSON_VALUE validieren und extrahieren müssen, um mit den Indizes und Ausdrucken zu wachsen . Es wird empfohlen, die Filterung regelmäßiger Routen durchzuführen.

JSON-Tabelle: FOR JSON und native Dateien

Da die Anwendung Ihrer API JSON erfordert, überträgt FOR JSON das Format auf den Motor. Mit PATH können Sie einen Alias ​​mit Punkten verwenden. Die automatische Ableitung der Struktur erfolgt durch Tabellen/Verknüpfungen. Ideal für OData, AJAX oder Frontends und JavaScript.

SELECT id,
       firstName AS "info.name",
       lastName  AS "info.surname",
       age,
       dateOfBirth AS dob
FROM People
FOR JSON PATH;

Außerdem verfügt es über JSON-Aggregate , um Strukturen aus folgenden Gruppen zu erstellen: JSON_OBJECTAGG (Objekte) und JSON_ARRAYAGG (Arrays). Vereinfacht die Erstellung aggregierter Payloads ohne SQL.

Verzeichnis, Indexierung und Bereitstellung auf SQL Server

Um vollständige Dokumente zu erhalten, ist die allgemeine Option nvarchar(max) (oder JSON nativ, wenn es verfügbar ist). Gültig mit CHECK(ISJSON(col)=1), wenn die Formatintegrität sichergestellt werden muss und die Schlüssel mit den Berechnungsspalten angezeigt werden, um die in den Datenbanken verwendeten Propiedades zu indexieren.

CREATE TABLE WebSite.Logs (
  [_id]  bigint IDENTITY PRIMARY KEY,
  [log]  nvarchar(max),
  [severity] AS JSON_VALUE([log], '$.severity'),
  INDEX ix_severity (severity)
);
-- Opcional: forzar JSON válido
ALTER TABLE WebSite.Logs
ADD CONSTRAINT CK_Log_JSON CHECK (ISJSON([log]) = 1);

Für Analysen und Archivierung wurde ein Columnstore-Index in die Komprimierungs-Portal-Sammlung aufgenommen und schnell gescannt. Wenn Sie sich mit der Erstellung/Aktualisierung von Dokumenten befassen, berücksichtigen Sie optimierte Tabellen zum Speichern (mit der Speicherung der unterstützten Tipps) und natürlich kompilierte Verfahren für Aktualisierungsvorgänge mit JSON_MODIFY.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log]  nvarchar(max)
) WITH (MEMORY_OPTIMIZED = ON);
GO
CREATE PROCEDURE WebSite.UpdateData
  @Id int, @Property nvarchar(100), @Value nvarchar(100)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (transaction isolation level = snapshot, language = N'English')
  UPDATE WebSite.Logs
  SET [log] = JSON_MODIFY([log], @Property, @Value)
  WHERE _id = @Id;
END;

Als gemischter Benutzer wird die Variable in JSON deaktiviert und die Beziehung beibehalten, sodass sie im Menü konsultiert werden kann . Zur Information, verwenden Sie OPENJSON mit WITH , um rechteckige Datensätze vorzubereiten, und fügen Sie JSON_VALUE()-Indizes hinzu, sobald die Route in Filtern/Ordnern verwendet wird.

Ladung und Import von Archiven und Diensten

Wenn Sie JSON von Dateien oder REST-Endpunkten erhalten, können Sie diese nach Bedarf laden und in SQL analysieren . Mit dem Zugriff auf Archiv-Orte oder -Abteilungen kann der Motor große Mengen an Operationen durchführen ; Für ursprüngliche Distributionen wie Hadoop oder Azure Blob ermöglicht PolyBase die Eingabe von JSON-Text und die Zuordnung mit OPENJSON/JSON_VALUE in T-SQL.

DECLARE @jsonVariable nvarchar(max) = N'[
 { "Order": {"Number":"SO43659", "Date":"2011-05-31T00:00:00"},
   "AccountNumber":"AW29825", "Item": {"Price":2024.9940, "Quantity":1}},
 { "Order": {"Number":"SO43661", "Date":"2011-06-01T00:00:00"},
   "AccountNumber":"AW73565", "Item": {"Price":2024.9940, "Quantity":3}}
]';

SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, '$') WITH (
  Number    varchar(200)  '$.Order.Number',
  Date      datetime      '$.Order.Date',
  Customer  varchar(200)  '$.AccountNumber',
  Quantity  int           '$.Item.Quantity'
) AS SalesOrderJsonData;

Sobald der native JSON- Typ auf Ihrer Plattform verfügbar ist, wurde die E/S-Effizienz und die Komprimierung genehmigt . Wenn nicht, werden nvarchar(4000) -Werte für die Bereitstellung von nvarchar(max) benötigt, da die Dokumente nicht größer als 8 KB sind.

Gebrauchs- und Hybridmodelle

Zu den besten Kandidaten für JSON in SQL gehören Datenmodelle mit Variablenattributen (E-Commerce), Telemetrie und Protokollen , halbstrukturiertes IoT mit zeitnaher Echtzeitanalyse und REST-Ausstellung von SQL in Frontends. Da die allgemeine Empfehlung nicht normalisiert werden muss, müssen Sie die Schlüssel zur Rücksprache mit den eskalierten Kolumnen und dem Index der kritischen Routen einhalten.

In Sicherheits- oder Internationalisierungsszenarien wird JSON als Text verwendet und profitiert von allen Motoreigenschaften (Zeittabellen, Sicherheits- und Filialebene usw.). Dies ist eine einheitliche politische Einrichtung und vermeidet Ausnahmen in gemischten Pipelines.

BigQuery: Natives JSON verwenden und GoogleSQL konsultieren

BigQuery bietet einen nativen JSON-Datentyp mit Zugriff auf Standorte für Punto- und Subindex-Betreiber . Sie können JSON-Werte mit JSON-Literalen , PARSE_JSON (konvertierter STRING in JSON), TO_JSON (konvertierter SQL-Typ in JSON) erstellen und Arrays mit JSON_ARRAY und Objekte mit JSON_OBJECT erstellen.

-- Crear tabla con columna JSON
CREATE OR REPLACE TABLE mydataset.table1 (
  id   INT64,
  cart JSON
);
-- Insertar literales JSON
INSERT INTO mydataset.table1 VALUES
  (1, JSON '{"name":"Alice","items":[{"product":"book","price":10},{"product":"food","price":5}]}'),
  (2, JSON '{"name":"Bob","items":[{"product":"pen","price":20}]}');

-- Acceso por punto y subíndice
SELECT cart.name, cart.items[0] AS first_item
FROM mydataset.table1;

Für Listen kann JSON_QUERY_ARRAY mit ARRAY<JSON> und UNNEST in Dateien erweitert werden. Wenn Sie ein Kompaktgerät benötigen, verwenden Sie ARRAY_AGG . Für Vergleiche/Ordnungen muss JSON_VALUE wie STRING SQL hochgestuft werden, und die Funktionen LAX_* (z. B. LAX_INT64 ) werden mit Toleranz konvertiert.

-- Expandir items y leer el campo product
SELECT id, JSON_VALUE(item.product) AS product
FROM mydataset.table1,
UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;

-- Conversión flexible de tipos
SELECT LAX_INT64(JSON '"10"') AS id; -- 10

Wichtig mit neuen Werten: Der JSON-Nullwert ist der SQL-NULLwert . JSON_QUERY kann Devolver null JSON enthalten; JSON_VALUE gibt NULL SQL aus, wenn es nicht erhöht wird. Bitte beachten Sie, dass Sie sich an Filtern und Filtern orientieren.

Lade- und Ladelimit: maximal 500 Anidación; Es ist nicht möglich, JSON-Spalten zu gruppieren (keine Vergleichsdefinition). Um CSV oder JSONL in Ladejobs zu verwenden, wird die Storage Write API für modernes Streaming oder die Übertragungs-API heruntergeladen und ausgeführt. In allen Fällen erhalten Sie Zugriff auf JSON über STRING.

Oracle und PostgreSQL: Äquivalente und Migration

In Oracle (12.2+) wandelt JSON_TABLE Dokumente in Dateien mit PATH in Spalten um und manipuliert Fehler. Um zu migrieren, dass AWS SCT keine automatischen JSON-Funktionen von Oracle in PostgreSQL konvertiert hat, müssen Sie das idiomatische System verwenden, um -> und ->> die meisten JSON-Aggregationsfunktionen zu verwenden.

In PostgreSQL -> JSON entwickeln und Text erstellen, damit CAST explizit erklärt und in Joins/Gruppen verwendet werden kann. Um Strukturen zu erstellen, verwenden Sie JSON_BUILD_OBJECT und JSON_AGG (entspricht JSON_OBJECT und JSON_ARRAYAGG von Oracle). Um die Rechnung zu erhalten, sind für Vergleiche numerische oder Rechnungstypen erforderlich.

-- Lectura en PostgreSQL: rutas jerárquicas y casteo con ->>
SELECT
  (json_doc::json -> 'data' -> 'account' ->> 'parentAccountNumber')::int AS parentAccountNumber,
  (json_doc::json -> 'data' -> 'account' ->> 'accountNumber')::int      AS accountNumber,
  (json_doc::json -> 'data' -> 'account' ->> 'businessUnitId')::int      AS businessUnitId,
  (json_doc::json -> 'data' ->> 'positionId')::varchar                   AS positionId
FROM aws_test_pg_table;

-- Agregación a JSON (equivalente conceptual a Oracle)
SELECT JSON_BUILD_OBJECT(
  'accountCounts', JSON_AGG(
     JSON_BUILD_OBJECT(
       'businessUnitId', businessUnitId,
       'parentAccountNumber', parentAccountNumber,
       'accountNumber', accountNumber,
       'totalOnlineContactsCount', online_contacts_count,
       'countByPosition', JSON_BUILD_OBJECT(
          'taxProfessionalCount', tax_professional_count,
          'attorneyCount',       attorney_count,
          'nonAttorneyCount',    non_attorney_count,
          'clerkCount',          clerk_count
       )
     )
  )
)
FROM (...) t; -- Agrega unifica y castea antes de construir el objeto

Praktische Konvertierungspraxis: JSON_TABLE mit json_array_elements simulieren , um Arrays zu erweitern, ->> verwenden, sobald die Konvertierung abgeschlossen ist und mit der Gruppe oder dem Unternehmen vergleichbare Typen garantiert werden . Dies verhindert, dass Getränke/Bestellung bestellt werden, und maximiert die Rendite mit den funktionalen Indizes von GIN in jedem Fall.

Implementierungs-, Sicherheits- und BI-Patronen

Für Architekturbüros kombinieren Sie JSON-Dokumente für digitale Attribute mit Beziehungsspalten für Handelsschlüssel . Fügen Sie Indizes in Spaltenberechnungen (SQL Server) oder GIN/Ausdrücke (PostgreSQL), Anwendungsvalidierungen ( CHECK ISJSON, JSON Schema in der App) und eine Exportplanung für BI-Modelle hinzu. Wir neigen dazu, die Situation zu verbessern, ohne die Kontrolle und Information zu verlieren.

Sicher, JSON wird wie andere wichtige Daten verwendet: Transport und Aufbewahrung, Zugangskontrolle, Auditorium und Systemüberwachung. Für die Analyse bereiten Sie sich auf ETL-Materialien vor , die JSON in Tabellenkalkulationen umwandeln, wenn die benötigte Hardware (z. B. Power BI ) erforderlich ist. Dieses Hybrid-Enfoque ist das Beste, was ich in gemischten Ladungen erreichen kann.

Ein praktisches Werkzeug für T-SQL: Arrays ohne Cursor aufzeichnen

Wenn Elemente aus einem JSON-Array aufgerufen werden müssen, vermeiden Sie die Verknüpfung von Routen in Blöcken. OPENJSON mit APPLY ist schneller, schneller und sicherer . Ein typischer Patron für ein Verfahren, bei dem Elemente aus einem Array in eine Tabelle eingefügt werden:

DECLARE @json nvarchar(max) = N'{
  "timeCardLogs": [
    {"day":"2023-07-03T00:00:00", "totalHours":"01:52"},
    {"day":"2023-07-04T00:00:00", "totalHours":"02:09"}
  ]
}';

-- Expandir el array y proyectar columnas tipadas
SELECT 
  JSON_VALUE(j.value, '$.day')        AS Day,
  JSON_VALUE(j.value, '$.totalHours') AS TotalHours
FROM OPENJSON(@json, '$.timeCardLogs') AS j;

-- Inserción ejemplo
-- INSERT INTO dbo.TimeCard(Day, TotalHours)
-- SELECT JSON_VALUE(j.value, '$.day'), JSON_VALUE(j.value, '$.totalHours')
-- FROM OPENJSON(@json, '$.timeCardLogs') AS j;

Da es sich um eine bessere Lesbarkeit handelt, können Indexfehler und Probleme mit den mit WHILE/Cursores verknüpften Dateien vermieden werden, und es wird für den Motor empfohlen, wenn es mit JSON-Sammlungen arbeitet.

Für Organisationen, die das Beste aus sich herausholen möchten, sind Spezialfirmen wie Q2BSTUDIO auf Hybrid-Implementierung, Cloud-Unterstützung (AWS/Azure), Sicherheit und Automatisierung mit IA spezialisiert . Der Schlüssel besteht aus einer Kombination von Datendesign mit JSON-Rendering auf der Basis, robusten Pipelines und Visualisierung in BI-Suiten, begleitet von Migrationen zwischen Motoren und den jeweiligen JSON-Funktionen.

Die Integration von JSON und SQL besteht aus einem leistungsstarken Quellcode (natürlich oder in Textform), der Nutzung der Funktionen jedes Motors (T-SQL, GoogleSQL, Postgres-Server, JSON_TABLE und Oracle) und der Bereitstellung von Indizes und Ansichten für die Abfrage von agilen APIs. Mit diesen Stücken können Sie sich anmelden, beraten, umwandeln und JSON bereitstellen, um die Verwaltung, Sicherheit und Bereitstellung auf der gesamten Plattform zu verbessern.